Oracle之表压缩和索引压缩

解析：压缩后的表更新的开销会更大，查询耗费的CPU也更多。所以压缩表一般适合在更新比较少，且CPU消耗不大，IO消耗很大系统中试用。

根据查询首先会看出block减少，然后 根据自行计划查看即可。

表压缩：

---压缩表可减少数据量，从而减少IO

DROP TABLE t purge;

CREATE TABLE t NOCOMPRESS AS
SELECT rownum AS n, rpad(' ',500,mod(rownum,15)) AS pad
FROM dual
CONNECT BY level <= 200000;

execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t');

--未压缩的表当前情况
SELECT table_name, blocks,compression  FROM user_tables WHERE table_name = 'T';

set autotrace traceonly
select count(*) from t;

--开始压缩表
set autotrace off
ALTER TABLE t MOVE COMPRESS;
execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t');
SELECT table_name, blocks,compression  FROM user_tables WHERE table_name = 'T';--查询block数量

set autotrace traceonly 
select count(*) from t;

索引压缩：

---压缩索引(联合索引的压缩层度会高一些）


DROP TABLE t1 purge;


CREATE TABLE t1 AS select * from dba_objects;
alter table T1 modify owner not null;
alter table T1 modify object_name not null;
alter table T1 modify object_type not null;
insert  into t1 select * from t1;
insert  into t1 select * from t1;
commit;
create index idx1_object_union  on t1(owner,object_type,object_name);
execute dbms_stats.gather_index_stats(ownname=>user, indname=>'idx1_object_union');

--未压缩索引的当前情况
SELECT t.index_name,t.compression,t.leaf_blocks,t.blevel  FROM user_indexes t WHERE index_name = 'IDX1_OBJECT_UNION';

--开始压缩索引
drop table t2 purge;
create table t2 as select * from t1;
alter table T2 modify owner not null;
alter table T2 modify object_name not null;
alter table T2 modify object_type not null;
create index idx2_object_union  on t2(owner,object_type,object_name);
ALTER index  idx2_object_union  rebuild  COMPRESS;
execute dbms_stats.gather_index_stats(ownname=>user, indname=>'idx2_object_union');


SELECT t.index_name,t.compression,t.leaf_blocks,t.blevel  FROM user_indexes t WHERE index_name = 'IDX2_OBJECT_UNION';--查看block数量

set linesize 1000
set autotrace traceonly
select count(*) from t1 ;

select count(*) from t2 ;