ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle压缩表(一)

oracle压缩表(一)

原创 Linux操作系统 作者:space6212 时间:2019-05-21 07:48:05 0 删除 编辑
我们知道,压缩表可以使同样的数据占用更少的空间,它对空间的使用、性能的提高都有积极的影响。下面简单探讨一下oracle的压缩表。

--建立测试表
SQL> create table t_compress1(a number,b number,c number) compress pctfree 0;

Table created

SQL> create table t_compress2(a number,b number,c number) compress pctfree 0;

Table created

SQL> create table t_compress3(a number,b number,c number) compress pctfree 0;

Table created

SQL> create table t_compress4(a number,b number,c number) compress pctfree 0;

Table created

SQL> create table t_uncompress(a number,b number,c number) pctfree 0;

Table created

SQL> create table t_compress5(a number,b number,c number) compress pctfree 0;

Table created

--生成测试数据

SQL> insert /*+ append */ into t_uncompress select rownum,mod(rownum,10000),mod(rownum,3) from all_objects,all_objects where rownum<300000;

299999 rows inserted

SQL> commit;

--以下内容是dump表上的一个block得到的trace文件摘取出来的
flag=-------- 这个表示block没有被压缩
ntab=1 --这个表示block有一个表的数据
nrow=483 --这个表示block上有483条记录

--直接路径insert
SQL> insert /*+ append */ into t_compress1 select * from t_uncompress order by a;

299999 rows inserted

SQL> commit;

Commit complete

--以下内容是dump表上的一个block得到的trace文件摘取出来的
flag=-0------这个表示block经过压缩
ntab=2 --这个表示block上存在2张表(其中一个为符号表,保留block中的重复数据的单个拷贝)
nrow=538 --这个表示block上有538条记录

SQL> insert /*+ append */ into t_compress2 select * from t_uncompress order by b;

299999 rows inserted

SQL> commit;

Commit complete

--以下内容是dump表上的一个block得到的trace文件摘取出来的
flag=-0------表示block经过压缩
ntab=2 --这个表示block上存在2张表的数据
nrow=705 --这个表示当前block上有705条记录

SQL> insert /*+ append */ into t_compress3 select * from t_uncompress order by c;

299999 rows inserted

SQL> commit;

Commit complete

--以下内容是dump表上的一个block得到的trace文件摘取出来的
flag=-0------表示block经过压缩
ntab=2--这个表示block上存在2张表的数据
nrow=503 --这个表示当前block上有503条记录


SQL> insert /*+ append */ into t_compress5 select * from t_uncompress;

299999 rows inserted

SQL> commit;

--以下内容是dump表上的一个block得到的trace文件摘取出来的
flag=-0------表示block经过压缩
ntab=2 --这个表示block上存在2张表的数据
nrow=538 --这个表示当前block上有538条记录

--普通方式insert
SQL> insert into t_compress4 select * from t_uncompress order by c;

299999 rows inserted

SQL> commit;

Commit complete

--以下内容是dump表上的一个block得到的trace文件摘取出来的
flag=--------表示block没有压缩
ntab=1 --这个表示block上存在1张表的数据
nrow=475 --这个表示当前block上有475条记录

SQL> select table_name,round((blocks-EMPTY_BLOCKS)/(select blocks-EMPTY_BLOCKS from user_tables where table_name='T_UNCOMPRESS')*100,2) pct from user_tables where table_name like '%T_COMPRESS%';

TABLE_NAME PCT
------------------------------ ----------
T_COMPRESS1 100
T_COMPRESS2 76.71
T_COMPRESS3 92.01
T_COMPRESS4 100.33
T_COMPRESS5 80.03

可以看出,order by的列(也就是压缩列)不同会导致压缩率的不同,其中根据B列压缩的压缩率最高。
我们看一下数据分布情况:

SQL> select column_name,NUM_NULLS,NUM_DISTINCT ,AVG_COL_LEN from user_tab_columns where table_name='T_UNCOMPRESS';

COLUMN_NAME NUM_NULLS NUM_DISTINCT AVG_COL_LEN
-------------------- ---------- ------------ -----------
A 0 299999 4
B 0 10000 3
C 0 3 2



从以上信息可以得到结论:
1、以选择性较低、长度较大的列作为压缩列会得到较高的压缩率。
如果列是选择性很高,则根据该列压缩没有意义,甚至可能压缩后占用空间比不压缩更高。
如果不指定order by,oracle会自动选择一个合适的列作为压缩列,但压缩率不一定是最高的。
2、普通的DML不会压缩数据。实际上,只有以下情况才会对数据进行压缩
o insert /*+ append */
o create table as select
o direct path loads
o moves

下面简单看看压缩表带来的性能方面的好处:
suk@SUK> select count(1) from t_uncompress;


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
624 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

suk@SUK> select count(1) from t_compress2;


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
490 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

可见,压缩表比没有压缩的表在全表扫描时,逻辑读明显减少了。
这是由于对表进行了压缩,表段占用的空间也小了。这些block被读到buffer时仍然是压缩的状态,所以这样可以减少数据库读取的IO,
也可以认为压缩表相当于扩充了SGA,因为同样的数据用了更少的内存。
当然,压缩表会会比非压缩表消耗多一点CPU资源,但是这个消耗是在可以接受的范围内的。
注意:压缩表只能用在静态表或者数据修改非常少的表中,否则会产生很大的负面影响:
1) 频繁的DML会使表空间使用率暴涨
因为普通的DML不会对数据进行压缩。insert与delete操作与非压缩表一样,但是update操作会使数据从压缩状态变更非压缩状态。
数据一经解压,占用的空间必然会增加。
2) 频繁的DML会产生很多的行迁移
同上一条原因一样,update操作会使数据从压缩状态变更非压缩状态,占用空间增加,必然会引起行迁移。
3) 频繁的DML会使CPU使用率增高
因为比普通的DML对了一个"解压"的过程

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63798/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle 10G特性之awr
请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    155
  • 访问量
    116674