对IOT表进行压缩与对普通表进行压缩一样。
首先创建一个iot表
create table iot
( owner, object_type, object_name,
constraint iot_pk primary key(owner,object_type,object_name)
)
organization index
NOCOMPRESS
as
select distinct owner, object_type, object_name
from all_objects
/
analyze index iot_pk validate structure;
select lf_blks, br_blks, used_space,
opt_cmpr_count, opt_cmpr_pctsave
from index_stats;
结果如下:
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
295 3 2118144 2 33
查询结果告诉我们,如果使用压缩,压缩列数是2是最优的,可以节约33%的空间。
先把压缩列数改为1
alter table iot move compress 1;
analyze index iot_pk validate structure;
select lf_blks, br_blks, used_space,
opt_cmpr_count, opt_cmpr_pctsave
from index_stats;
查询结果如下:
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
257 1 1842725 2 23
从结果中可以看出占用空间有所减少。
alter table iot move compress 2;
analyze index iot_pk validate structure;
select lf_blks, br_blks, used_space,
opt_cmpr_count, opt_cmpr_pctsave
from index_stats;
这次的查询结果是:
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
197 1 1412441 2 0
可以看到确实减少了33%的空间((2118144-1412441)/2118144)。
其实没有那么麻烦,只要在IOT创建时,指定为压缩,但不指出压缩的列数,就可以达到最优压缩了。
create table iot
( owner, object_type, object_name,
constraint iot_pk primary key(owner,object_type,object_name)
)
organization index
COMPRESS
as
select distinct owner, object_type, object_name
from all_objects
/
analyze index iot_pk validate structure;
select lf_blks, br_blks, used_space,
opt_cmpr_count, opt_cmpr_pctsave
from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
--------- ---------- ---------- -------------- ----------------
197 1 1412395 2 0
参考文献
《Oracle9i&10g编程艺术》