My Oracle Support Banner

Index Is Not Used If Defined On a CHAR Column That Is TDE Encrypted And WHERE Clause Uses Binds (Doc ID 1470350.1)

Last updated on APRIL 09, 2018

Applies to:

Advanced Networking Option - Version 10.2.0.5 and later
Information in this document applies to any platform.

Symptoms

The premises of this issue are as follows:
1. create an encrypted column of datatype CHAR and encryption NO SALT.
2. create an index on this encrypted column.
3. run a query that uses a WHERE clause with bind variables on the encrypted column
The query would access the table using Full Table Scan access path.
The issue does not reproduce if using another datatype for the encrypted column or if using literals instead of bind variables.
A succint example is given below:


conn / as sysdba
drop user test cascade;
create user test identified by test;
grant dba to test;

conn test/test

create table tbl1
(
col1 char(20) encrypt no salt,
col2 number
);

create index tbl1_col1_ix on tbl1(COL1);

begin
 for i in 1..10000 loop
 insert into tbl1 values('col1'||i,i);
 commit;
 end loop;
end;
/

execute dbms_stats.gather_schema_stats('TEST');

conn test/test

variable v_col1 char(19);
execute :v_col1:='col110';

--Then generate either the 10046 or 10053 and check the resulting trace:

alter session set events='10053 trace name context forever, level 1';
alter session set events='10046 trace name context forever, level 8';

select t1.*
from tbl1 t1
where t1.col1=:v_col1;

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |    25 |           |
| 1   |  TABLE ACCESS FULL | TBL1    |   100 |  2500 |    25 |  00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(INTERNAL_FUNCTION("T1"."COL1")=:V_COL1)

select t1.*
from tbl1 t1
where t1.col1='col110';

============
Plan Table
============
---------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name        | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |             |       |       |     1 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | TBL1        |     1 |   102 |     1 |  00:00:01 |
| 2   |   INDEX RANGE SCAN           | TBL1_COL1_IX|     1 |       |     1 |  00:00:01 |
---------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T1"."COL1"='COL110')

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.