jump to navigation

Indexing and Transparent Data Encryption Part III (You Can’t Do That) June 16, 2015

Posted by Richard Foote in Oracle Indexes.
add a comment

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.