ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > 压缩table and index 对　space 的影响test

压缩table and index 对　space 的影响test

原创 Linux操作系统 作者：zhouxianwang 时间：2012-08-14 19:38:56 0 删除 编辑

一. 创建两个不同(压缩与非压缩)表，空间的比较

       SQL> create table t_compress compress as select * from dba_objects;
      
       Table created.
      
       SQL> create table t_uncompress as select * from dba_objects;
      
       SQL> select segment_name,bytes from user_extents where segment_name in ('T_UNCOMPRESS');
      
       SEGMENT_NAME                        BYTES
       ------------------------------ ----------
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                        65536
       T_UNCOMPRESS                      1048576
       T_UNCOMPRESS                      1048576
       T_UNCOMPRESS                      1048576
       T_UNCOMPRESS                      1048576
       T_UNCOMPRESS                      1048576
       T_UNCOMPRESS                      1048576
      
       22 rows selected.
      
       SQL> select segment_name ,bytes from user_extents where segment_name in ('T_COMPRESS');
      
       SEGMENT_NAME                        BYTES
       ------------------------------ ----------
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                          65536
       T_COMPRESS                        1048576
       T_COMPRESS                        1048576
      
       18 rows selected.
      
      
      
       查看表的大小：
      
      
       SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'t_compress',cascade=>true);
      
       PL/SQL procedure successfully completed.
      
       SQL> exec dbms_stats.gather_table_stats('test',tabname=>'t_uncompress',cascade=>true);
      
       PL/SQL procedure successfully completed.
      
       SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in  ('T_COMPRESS','T_UNCOMPRESS');
      
       SEGMENT_NAME                                                                        BYTES_MB
       --------------------------------------------------------------------------------- ----------
       T_UNCOMPRESS                                                                               7
       T_COMPRESS                                                                                 3

 

INDEX 的压缩：
SQL> create table t10 as select * from dba_objects;

Table created.
SQL> insert into t10 select * from t10;

60689 rows created.

SQL> r
  1* insert into t10 select * from t10

121378 rows created.

SQL> r
  1* insert into t10 select * from t10

242756 rows created.

SQL> commit;

Commit complete.

SQL> create table t20 as select * from t10;

Table created.

SQL> commit;

SQL> create  index ind_t10_compress on t10(object_name) compress;

Index created.

SQL> create index ind_t20_uncompress on t20(object_name);

Index created.

SQL> commit;

SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in  ('IND_T10_COMPRESS','IND_T20_UNCOMPRESS');

SEGMENT_NAME                     BYTES_MB
------------------------------ ----------
IND_T10_COMPRESS                        8
IND_T20_UNCOMPRESS                     19

##################################################################################################

 

二、　当列上的键值重复率高时，索引的压缩就会较大，

      SQL> create table t21 as select object_id,object_name from dba_objects;
     
      Table created.
     
     
      SQL> CREATE table t22 as select 1 object_id,object_name from dba_objects;
     
      SQL> create index ind_t21 on t21(object_id) compress;
     
      Index created.
     
      SQL> create index ind_t22 on t22(object_id) compress;
     
      Index created.
     
     
      SQL> select count(distinct object_id) from t21;
     
      COUNT(DISTINCTOBJECT_ID)
      ------------------------
                         60685
     
      SQL> select count(distinct object_id) from t22;
     
      COUNT(DISTINCTOBJECT_ID)
      ------------------------
                             1
     
      SQL> select count(1) from t21;
     
        COUNT(1)
      ----------
          485544
     
      SQL> select  count(1) from t22;
     
        COUNT(1)
      ----------
          485552
     
      SQL> exec dbms_stats.gather_table_stats('test',tabname=>'t21',cascade=>TRUE);
     
      PL/SQL procedure successfully completed.
     
      SQL> exec dbms_stats.gather_table_stats('test',tabname=>'t22',cascade=>true);
     
      PL/SQL procedure successfully completed.
     
     
      SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in  ('IND_T21','IND_T22');
     
      SEGMENT_NAME                                                                        BYTES_MB
      --------------------------------------------------------------------------------- ----------
      IND_T21                                                                                    2
      IND_T22                                                                                .8125
     
     
      SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_extents where segment_name in  ('IND_T21');
     
      SEGMENT_NAME                                                                        BYTES_MB
      --------------------------------------------------------------------------------- ----------
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
     
      SEGMENT_NAME                                                                        BYTES_MB
      --------------------------------------------------------------------------------- ----------
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                .0625
      IND_T21                                                                                    1
     
      17 rows selected.
     
      SQL>
      SQL> select segment_name,bytes/1024/1024 Bytes_MB from user_extents where segment_name in  ('IND_T22');
     
      SEGMENT_NAME                                                                        BYTES_MB
      --------------------------------------------------------------------------------- ----------
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625
     
      SEGMENT_NAME                                                                        BYTES_MB
      --------------------------------------------------------------------------------- ----------
      IND_T22                                                                                .0625
      IND_T22                                                                                .0625

13 rows selected.

SQL>
     

######################################################################

 

正常的INSERT　的数据并不会被压缩：   
    
     SQL> create table t31  compress as select * from dba_objects;
    
     Table created.
    
     SQL> create table t32  compress as  select * from dba_objects where 1=2;
    
     Table created.
    
     SQL> insert into t32 select * from dba_objects;
    
     60626 rows created.
    
     SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'t31',cascade=>true);
    
     PL/SQL procedure successfully completed.
    
     SQL> exec dbms_stats.gather_table_stats('test',tabname=>'t32',cascade=>true);
    
     SQL>  select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in  ('T31','T32');
    
     SEGMENT_NAME                                                                        BYTES_MB
     --------------------------------------------------------------------------------- ----------
     T31                                                                                        3
     T32                                                                                        6    
    
    
     UPDATE 对空间的影响：（解压宿） 
    
    
    
     SQL> update t31 set object_name=object_name||'decompress';

60625 rows updated.

SQL>  select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in  ('T31','T32');

SEGMENT_NAME                                                                        BYTES_MB
--------------------------------------------------------------------------------- ----------
T31                                                                                        8
T32                                                                                        6

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'t31',cascade=>true);

PL/SQL procedure successfully completed.

SQL>  select segment_name,bytes/1024/1024 Bytes_MB from user_segments where segment_name in  ('T31','T32');

SEGMENT_NAME                                                                        BYTES_MB
--------------------------------------------------------------------------------- ----------
T31                                                                                        8
T32                                                                                        6                                                                     3

 

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

0
0
上一篇： RAC中的跨节点并行[转]
下一篇： AWR 学习－－留一个笔记
请登录后发表评论 登录
全部评论
zhouxianwang

注册时间：2012-06-04

  • 博文量
    144
  • 访问量
    493864

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593