oracle压缩表缺点,oracle 11g对于表压缩改进

最新推荐文章于 2021-04-09 12:05:54 发布

杭亮

最新推荐文章于 2021-04-09 12:05:54 发布

阅读量508

点赞数

文章标签： oracle压缩表缺点

测试目的：测试11g压缩性能

测试用表

1.test表为导入数据表

2.cm表为普通表未设置compress，普通insert插入

3.cm_cmp表为设置compress,普通insert插入

4.cm_app表为设置compress，insert apped插入

5.cm_ldr表为设置compress,sqlldr导入

6.cm_for_all表设置compress for all operations，普通insert插入

具体测试过程

SQL> select * from v$version where rownum<2;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production

SQL> conn xh/a831115

Connected.

SQL> show user

USER is "XH"

SQL> create table test (a char(20),b char(20));

Table created.

SQL>declare

2begin

3for i in1..1000 loop

4insert into test values('a','b');

5end loop;

6for i in1..1000 loop

7insert into test values('a'||i,'b'||i);

8end loop;

9commit;

10end;

11/

PL/SQL procedure successfully completed.

SQL>execute dbms_stats.gather_table_stats('XH','TEST');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME ,num_distinct,num_nulls from user_tab_col_statistics where table_name='TEST';

COLUMN_NAMENUM_DISTINCTNUM_NULLS

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

A10010

B10010

SQL> select num_rows from user_tables where table_name='TEST';

NUM_ROWS

----------

2000

Test表有a,b字段共2000行，其中重复字段1000行，按压缩表性质数据字段原位留指针,指向块头的符号表，此表理想压率应接近40%，使用测试表方便直观估计最佳压缩比率了解压缩性质，压缩算法未变.

建立测试表

SQL> create table cm as select * from testwhere 0=1;

Table created.

SQL> create table cm_cmpcompress as select * from testwhere 0=1;

Table created.

SQL> create table cm_appcompress as select * from testwhere 0=1;

Table created.

SQL> create table cm_ldrcompress as select * from testwhere 0=1;

Table created.

SQL> create table cm_for_all compress for all operationsas select * from testwhere 0=1;

Table created

查看表压缩性质

SQL> select table_name,num_rows,compression,compress_for from user_tables;

TABLE_NAMENUM_ROWS COMPRESS COMPRESS_FOR

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

TEST2000 DISABLED

XHT67424 DISABLED

CMDISABLED

CM_CMPENABLEDDIRECT LOAD ONLY

CM_APPENABLEDDIRECT LOAD ONLY

CM_LDRENABLEDDIRECT LOAD ONLY

CM_FOR_ALLENABLEDFOR ALL OPERATIONS

7 rows selected.

普通insert插入cm表(表未设置compress)

SQL>insert into cmselect * from test;

2000 rows created.

SQL> commit;

Commit complete.

SQL> col segment_name for a30

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM';

SEGMENT_NAMEFILE_IDBLOCK_IDBLOCKS

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

CM436578

CM437058

SQL> select count(distinct block#)from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM);

COUNT(DISTINCTBLOCK#)

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

结果：2个区，实际使用13个块

对cm表进行move compress

SQL> alter table cm move compress;

Table altered.

SQL> select count(distinct block#)from (select dbms_rowid.rowid_relative_fno(owed) file#,dbms_rowid.rowid_block_number(owed) block# ,owed from CM);

COUNT(DISTINCTBLOCK#)

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name=’CM’;

SEGMENT_NAMEFILE_IDBLOCK_IDBLOCKS

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

CM 437458

CM437538

结果：2个区，实际使用8个block，可以看到最佳可以压缩到8个block

对cm_cmp表(表设置为compress)，进行普通insert

SQL>insert into cm_cmpselect * from test;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM_CMP';

SEGMENT_NAMEFILE_IDBLOCK_IDBLOCKS

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

CM_CMP436658

CM_CMP437138

SQL> select count(distinct block#)from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM_CMP);

COUNT(DISTINCTBLOCK#)

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

结果：2个区，实际占用12个block，针对表设置compress后普通insert只压缩1个block(version 11g)

Cm_app表执行append insert

SQL> insert /*+append*/into cm_app select * from test;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name=’CM_APP’;

SEGMENT_NAMEFILE_IDBLOCK_IDBLOCKS

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

CM_APP436738

CM_APP437218

SQL> select count(distinct block#)from (select dbms_rowid.rowid_relative_fno(owed) file#,dbms_rowid.rowid_block_number(owed) block# ,owed from CM_APP);

COUNT(DISTINCTBLOCK#)

结果：2个区，实际使用8个block

Cm_ldr表进行sqlldr导入

[oracle@vm11g sqlldr]$ vi cm_ldr.ctl

load data

infile cm_ldr.dat

append

into table cm_ldr

FIELDS TERMINATED BY WHITESPACE

(A,B)

[oracle@vm11g sqlldr]$ sqlldr xh/a831115 direct=y

control = cm_ldr.ctl

SQL> select count(*) from cm_ldr;

COUNT(*)

----------

2000

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM_LDR';

SEGMENT_NAMEFILE_IDBLOCK_IDBLOCKS

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

CM_LDR436818

CM_LDR437298

SQL> select count(distinct block#)from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM_LDR);

COUNT(DISTINCTBLOCK#)

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

结果：2个区,实际使用8个block与append一致

Cm_for_all表普通insert

SQL> insert into cm_for_all select * from test;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM_FOR_ALL';

SEGMENT_NAMEFILE_IDBLOCK_IDBLOCKS

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

CM_FOR_ALL436978

CM_FOR_ALL437378

SQL> select count(distinct block#)from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM_FOR_ALL);

COUNT(DISTINCTBLOCK#)

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

结果：针对设置成compress for all的表普通insert压缩效果也是很理想，2个区，实际占用9个block

测试11g compress是否对执行UPDATE将造成row migrate表变大进行解决.

SQL> analyze table cm compute statistics;

Table analyzed.

SQL> select num_rows,chain_cnt from dba_tables where table_name='CM' ;

NUM_ROWSCHAIN_CNT

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

20000s

现在表无行迁移

SQL> update cm set A='AA';

2000 rows updated.

SQL> COMMIT;

Commit complete.

SQL> analyze table cm compute statistics;

Table analyzed.

SQL> select num_rows,chain_cnt from dba_tables where table_name='CM';

NUM_ROWSCHAIN_CNT

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

2000994

表出现大量行迁移，通过dump block看到nrid:0x01000944.0~~~~ROW migrate指向迁移到的block(通过NRID找到迁移行所在的block)

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM';

SEGMENT_NAMEFILE_IDBLOCK_IDBLOCKS

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

CM437458

CM437538

CM436578

可以看到多了一个区

综合结果：

在11g上针表设置compress后默认是ENABLEDDIRECT LOAD ONLY，既只有通过直接路径插入append ,sqlldr才可以压缩，压缩比率与正常move compress最佳比率一致，apped ,sqlldr无明显区别(oracle按插入类型分类，直接插入类型压缩算法一致),当表设置compress for all operations后，普通插入既可压缩，但压缩比率没有直接插入压缩比率好，但结果接近，这个新特性比较理想，但11g仍然在解压缩时候造成row migrate。

数据结果：

普通表普通insert，为13个block，move compress得到最佳压缩为8个block，压缩比为38%

表设置compress后普通insert为12个block，压缩比为7%

表设置compress后append ,为8个block，压缩比为38%

表设置compress后sqlldr，为8个block，压缩比为38%

表设置compress for all operations后普通insert，为9个block，压缩比为30%

11gr2有了for oltp将得到更好的压缩效果(针对普通insert)