一下内容来自《oracle9i&10g编程艺术》
create table t
as
select * from all_objects;
在owner、object_type、object_name这三列上创建索引,这三列基数大小关系为object_name > object_type > owner
create index t_idx1 on t(owner, object_type, object_name)
/
create index t_idx2 on t(object_name, object_type, owner)
/
naruto@ORCL> select count(distinct owner), count(distinct object_type),
2 count(distinct object_name), count(*)
3 from t
4 /
COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_TYPE) COUNT(DISTINCTOBJECT_NAME) COUNT(*)
-------------------- -------------------------- -------------------------- ----------
27 35 29634 49994
naruto@ORCL>analyze index t_idx1 validate structure
2 /
索引已分析
naruto@ORCL>select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave
2 from index_stats;
BTREE_SPACE PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
2792096 89 2 28
naruto@ORCL>analyze index t_idx2 validate structure
2 /
索引已分析
naruto@ORCL>select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave
2 from index_stats;
BTREE_SPACE PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
2792096 89 1 13
由OPT_CMPR_COUNT和OPT_CMPR_PCTSAVE这两列就很容易的看出t_idx1的最优压缩数为2,可以压缩28%的空间。t_idx2的最优压缩数为1,可以压缩13%的空间。把基数小的列放在创建索引时的前面可以减少空间的利用率。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25361369/viewspace-697521/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25361369/viewspace-697521/