oracle高度压缩表,oracle11g 表压缩

上次听oracle培训就讲到oracle11g 的新特性，advanced compress，可惜的是只支持在exadata的机器上，不过看文档，oracle11g的压缩也有新的改进，可以指定压缩的类型：for oltp,并且支持常规的单行插入。下面做个测试和10g对比一下：

10.2.0.3 环境：

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Linux: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

SQL> select count(*) from dba_objects;

COUNT(*)

----------

49856

SQL> create table compressed1 compress as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'COMPRESSED1',estimate_percent => null);

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name='COMPRESSED1'; 这里批量插入，压缩后的块数

BLOCKS

----------

200

下面我们随机插入：

SQL> create table compressed2 compress  as select * from dba_objects where 1=0;

Table created.

SQL> declare

2   type t_type is record

3   (owner compressed2.owner%type,

4   object_name compressed2.object_name%type,

5   subobject_name compressed2.subobject_name%type,

6   object_id compressed2.object_id%type,

7   data_object_id compressed2.data_object_id%type,

8   object_type compressed2.object_type%type,

9   created compressed2.created%type,

10   last_ddl_time compressed2.last_ddl_time%type,

11   timestamp compressed2.timestamp%type,

12   status compressed2.status%type,

13   temporary compressed2.temporary%type,

14   generated compressed2.generated%type,

15   secondary compressed2.secondary%type

16   --namespace compressed2.namespace%type,

17   --edition_name compressed2.edition_name%type

18   );

19   type t_type_array is table of t_type index by binary_integer;

20   test_array t_type_array;

21   test_re t_type;

22  begin

23

24  select * bulk collect into test_array from dba_objects;

25  for i in 1..test_array.count loop

26   execute immediate 'insert into compressed2 values(:r1,:r2,:r3,:r4,:r5,:r6,:r7,:r8,:r9,:r10,:r11,:r12,:r13)' using test_array(i).owner,

27   test_array(i).object_name,test_array(i).subobject_name,test_array(i).object_id,test_array(i).data_object_id,test_array(i).object_type,

28   test_array(i).created,test_array(i).last_ddl_time,test_array(i).timestamp,test_array(i).status,test_array(i).temporary,

29   test_array(i).generated,test_array(i).secondary;

30   commit;

31   end loop;

32   end;

33   /

SQL>  exec dbms_stats.gather_table_stats(user,'COMPRESSED2',estimate_percent => null);

PL/SQL procedure successfully completed.

SQL>  select blocks from user_tables where table_name='COMPRESSED2';可以看到这里几乎没有压缩：

BLOCKS

----------

616

SQL> create table compressed3 as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'COMPRESSED3',estimate_percent => null);

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name='COMPRESSED3';

BLOCKS

----------

684

接下来在 11g环境中测试：

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> create table compressed1 compress for oltp as select * from dba_objects where rownum

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'COMPRESSED1',estimate_percent => null);

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name='COMPRESSED1';

BLOCKS

----------

286

SQL> create table compressed2 compress for oltp as select * from dba_objects where 1=0;

Table created.

SQL> declare

2   type t_type is record

3   (owner compressed2.owner%type,

4   object_name compressed2.object_name%type,

5   subobject_name compressed2.subobject_name%type,

6   object_id compressed2.object_id%type,

7   data_object_id compressed2.data_object_id%type,

8   object_type compressed2.object_type%type,

9   created compressed2.created%type,

10   last_ddl_time compressed2.last_ddl_time%type,

11   timestamp compressed2.timestamp%type,

12   status compressed2.status%type,

13   temporary compressed2.temporary%type,

14   generated compressed2.generated%type,

15   secondary compressed2.secondary%type,

16   namespace compressed2.namespace%type,

17   edition_name compressed2.edition_name%type);

18   type t_type_array is table of t_type index by binary_integer;

19   test_array t_type_array;

20   test_re t_type;

21  begin

22

23  select * bulk collect into test_array from dba_objects where rownum

24  for i in 1..test_array.count loop

25   execute immediate 'insert into compressed2 values(:r1,:r2,:r3,:r4,:r5,:r6,:r7,:r8,:r9,:r10,:r11,:r12,:r13,:r14,:r15)' using test_array(i).owner,

26   test_array(i).object_name,test_array(i).subobject_name,test_array(i).object_id,test_array(i).data_object_id,test_array(i).object_type,

27   test_array(i).created,test_array(i).last_ddl_time,test_array(i).timestamp,test_array(i).status,test_array(i).temporary,

28   test_array(i).generated,test_array(i).secondary,test_array(i).namespace,test_array(i).edition_name;

29   commit;

30   end loop;

31   end;

32   /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'COMPRESSED2',estimate_percent => null);

SQL> select blocks from user_tables where table_name='COMPRESSED2'; 可以看这里虽然随机插入，但是还是起到了压缩的效果。

BLOCKS

----------

370

SQL> create table compressed3  as select * from dba_objects where rownum

SQL>  exec dbms_stats.gather_table_stats(user,'COMPRESSED3',estimate_percent => null);

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name='COMPRESSED3';

BLOCKS

----------

725

可以看到在11g中即使不是批量的插入，压缩的比率还是不错的。

由于两个库dba_objects的表结构以及数据都不是完全相同，只是大概类似，所以比较的数据不是很精确,主要看压缩比，还是可以看出11g中压缩的改进。

对于后续使用，不过还需要更详细的测试。