RE: TDE and execution plans

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 11 Mar 2014 15:32:51 +0000


I'd run up a few samples of the things you want to do, but you've picked a 
critical point.

If you encrypt a tablespace, blocks are encrypted on disc, but not in memory. 
(Any attempt to dump an in-memory block in the normal way will result in a line 
in the trace file saying something like "block not dumped as it is encrypted"). 
 This means that encryption appears to have no impact on indexing strategies.

If you encrypt a column in a table, it is effectively turned into a 
deterministically random value. and it becomes irrelevant to apply a 
range-based predicate to the column (a bit like reverse key indexes), although 
multiple rows with the same column value will end up with the same encrypted 
value and "column = {constant}" will report an index range scan.

It is important to note two other things as side effects of column encryption.  
You cannot do referential integrity between encrypted columns. The size of an 
encrypted value usually be larger than the size of the unencrypted value - the 
last example I did the encrypted values were rounded up to multiples of 16 
bytes, with an extra 16 bytes on top of that if I had a (on by default) salt. 
If you want to create an index at all, the column has to be encrypted without 
salt.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf 
of Hameed, Amir [Amir.Hameed@xxxxxxxxx]
Sent: 11 March 2014 15:18
To: oracle-l@xxxxxxxxxxxxx
Subject: TDE and execution plans

Hi,
We are looking into implementing the Oracle eBilling software and will most 
likely deploy it in a PCI compliant architecture and configuration. As part of 
securing sensitive data on disk, we may use the TDE feature of Oracle RDBMS 
(11.2.0.3/11.2.0.4) and it could be at the tablespace level or at the column 
level. As I understand it, TDE might have an impact on some of the execution 
plans in that for indexes that contain encrypted columns, the optimizer might 
not perform range scans and opt for FTS instead. This is primarily driven by 
the way encrypted columns are sorted and stored in indexes before and after 
encryption.
Does anyone have a first-hand experience with TDE and would like to share their 
experience in the context of performance overhead and issues?

Any feedback will be appreciated.

Regards,
Amir

Other related posts: