在多列上创建索引时列的顺序对索引大小的影响

一下内容来自《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/