由于显而易见的原因,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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22966231/viewspace-1029713/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22966231/viewspace-1029713/