oracle 11g表空间,Oracle 11g的表空间加密

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

如下：

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

Go to My Oracle Blog