Indexing and Transparent Data Encryption Part III (You Can’t Do That) June 16, 2015
Posted by Richard Foote in Oracle Indexes.trackback
In Part II of this series, we looked at how we can create a B-Tree index on a encrypted column, providing we do not apply salt during encryption.
However, this is not the only restriction with regard to indexing an encrypted column using column-based encryption.
If we attempt to create an index that is not a straight B-Tree index, for example a Bitmap Index:
SQL> create bitmap index bowie_code_i on bowie(code); create bitmap index bowie_code_i on bowie(code) * ERROR at line 1: ORA-28337: the specified index may not be defined on an encrypted column
Or a Function-Based Index:
SQL> create index bowie_code_i on bowie(code+10); create index bowie_code_i on bowie(code+10) * ERROR at line 1: ORA-28337: the specified index may not be defined on an encrypted column
We note that such indexes can’t be defined on an encrypted column. Only standard B-Tree Indexes are supported on specifically encrypted columns.
The B-Tree index can potentially be used with equality predicates:
SQL> set autotrace on
SQL> select * from bowie where id=42;
ID CODE SALARY TEXT
---------- ---------- ---------- ------------------------------
42 42 42 BOWIE
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE | 1 | 28 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
1 physical reads
0 redo size
748 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As we can see, the CBO has successfully used the index. However, if we attempt to use SQL with a non-equality predicate, such as a bounded range scan:
SQL> select * from bowie where id between 42 and 43;
ID CODE SALARY TEXT
---------- ---------- ---------- ------------------------------
42 42 42 BOWIE
43 42 43 BOWIE
Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BOWIE | 2 | 34 | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("ID")>=42 AND
INTERNAL_FUNCTION("ID")<=43)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
814 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
Or an unbounded range scan:
SQL> select * from bowie where id > 10000000000;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 124 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BOWIE | 1 | 124 | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("ID")>10000000000)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
108 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
540 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
We notice the index is ignored and can’t be used by the CBO.
So on an encrypted column, only a standard B-Tree index with an equality based predicate can be used. These restrictions don’t however apply to indexes based on tables within encrypted tablespaces.


Comments»
No comments yet — be the first.