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.
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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: