索引列顺序对索引数据量及性能的影响？

viadeazhu

原帖由 Germin 于 2009-10-28 22:20 发表
模拟了一些数据，

空间占用相同，clustering factor也一致

针对查询select * from T where a=? and b=? 默认选择使用索引i_ba
能否使用hints强制使用i_ab？ 不会写

感觉还是没能了解其内部有啥区别以及使用中的影响。

其实如果是select * from T where a=? and b=?两个index能得到几乎相同的效率。
但是我们需要考虑应用中是否有其他的SQL，比如select * from T where b=?。
如果考虑到这一个因素，就该吧b写在index的前面。

btw:
hint写法：
select /*+index(test test_ba)*/ * from test where a = 10 and b = 1000;
select /*+index(test test_ab)*/ * from test where a = 10 and b = 1000;

Yong Huang

原帖由 sqysl 于 2009-10-28 19:35 发表
哦，select * from large where a = 10 and b = 10;  
large表，应该为test吧

Sorry for my mistake. I tested earlier with large and later renamed it to test. But pasted one line of wrong code. Just corrected it.

Yong Huang

Byte_X_

原帖由 viadeazhu 于 2009-10-29 09:45 发表


其实如果是select * from T where a=? and b=?两个index能得到几乎相同的效率。
但是我们需要考虑应用中是否有其他的SQL，比如select * from T where b=?。
如果考虑到这一个因素，就该吧b写在index的前面。

btw:
hint写法：
select /*+index(test test_ba)*/ * from test where a = 10 and b = 1000;
select /*+index(test test_ab)*/ * from test where a = 10 and b = 1000;

测试过很多次了，确是这样。

fan_zhen_hua

原帖由 Byte_X_ 于 2009-10-30 16:09 发表


测试过很多次了，确是这样。

这个今天刚刚测试过。确实是这样的，占用空间其实一样，效率上也差不多。如果启用压缩的话。
a,b列的值分布不同，
列a的不同值个数较小，约20个
列b的不同值个数较大，约1000万
用a来作引导列当然可以更加节省空间。
最主要看你在应用过程中，是否有使用b作引导列。如果有诸如where b=?，则使用b,a会更好。此外，还应该考虑索引块竞争等因素。
TOM的编程艺术中有索引一节中对这个问题也有讲述，以下是测试的脚本。可以试下。
create table t
as
select * from all_objects;
create index t_idx_1 on t(owner,object_type,object_name);
create index t_idx_2 on t(object_name,object_type,owner);
select count(distinct owner), count(distinct object_type),
count(distinct object_name ), count(*)
from t;
analyze index t_idx_1 validate structure;
select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave
from index_stats;
analyze index t_idx_2 validate structure;
select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave
from index_stats;
alter session set sql_trace=true;
declare
        cnt int;
begin
  for x in ( select /*+FULL(t)*/ owner, object_type, object_name from t )
  loop
       select /*+ INDEX( t t_idx_1 ) */ count(*) into cnt
         from t
        where object_name = x.object_name
          and object_type = x.object_type
          and owner = x.owner;

        select /*+ INDEX( t t_idx_2 ) */ count(*) into cnt
         from t
        where object_name = x.object_name
          and object_type = x.object_type
          and owner = x.owner;
  end loop;
end;
/

fan_zhen_hua

Byte_X_ ，哈哈！~~