Go to My Oracle Blog

由于显而易见的原因，10.2引入的TDE功能对列加密之后，导致相关索引丧失了对不等式的判断能力。

如下：
ENCRYPTIONDEMO@fmw//scripts> create table tde_table(id number encrypt no salt,na
me varchar2(30))
2 ;

Table created.

Elapsed: 00:00:00.11
ENCRYPTIONDEMO@fmw//scripts> begin
2 for i in 1..20000 loop
3 insert into tde_table values (i,i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

ENCRYPTIONDEMO@fmw//scripts> create unique index tde_idx on tde_table(id);

Index created.

等式判断当然没有受到影响。

ENCRYPTIONDEMO@fmw//scripts> select * from tde_table where id=9;
Elapsed: 00:00:00.00
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| 0 | SELECT STATEMENT | | 1 | 64 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TDE_TABLE | 1 | 64 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | TDE_IDX | 1 | | 1 (0)| 00:00:01 |

不等式就尴尬了。

ENCRYPTIONDEMO@fmw//scripts> select * from tde_table where id<2;
Elapsed: 00:00:00.06
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 192 | 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TDE_TABLE | 3 | 192 | 69 (2)| 00:00:01 |

有意思的是11g提供了一个新功能: 表空间加密。 规格上说是可以对保存在其中的所有数据加密，其实，不但如此，索引也具备不等式判断能力。
如下：
创建一个加密的表空间
SYS@fmw//scripts> create tablespace securets datafile 'D:APPDBORACLE11GORADATAFMWSECURETS01.DBF' size 20M autoextend off encryption

using 'AES128' default storage (encrypt);

Tablespace created.

ENCRYPTIONDEMO@fmw//scripts> create table st (id number,name varchar2(30)) tablespace securets;

Table created.

Elapsed: 00:00:00.04
ENCRYPTIONDEMO@fmw//scripts> begin
2 for i in 1..20000 loop
3 insert into st values (i,i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.07

ENCRYPTIONDEMO@fmw//scripts> create unique index st_idx on st (id) tablespace securets;

Index created.

Elapsed: 00:00:00.10

不等式下索引照常工作。

ENCRYPTIONDEMO@fmw//scripts> select * from st where id<2;
Elapsed: 00:00:00.01

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ST | 1 | 30 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ST_IDX | 1 | | 2 (0)| 00:00:01 |

Todd