如何构建高BLEVEL的INDEX？

[i=s] 本帖最后由 wei-xh 于 2012-5-9 13:00 编辑

思考一个问题：有一个查询select * from table where id=:1 and name=:2;
单从这一个SQL来看，该如何建立索引。ID区分度足够好，NAME区分度稍微差点。
是ID,NAME好呢？还是NAME,ID好？为什么？
实验一把，看看。

CODE:

test@CRMD>create table test (id number ,name varchar2(4000));
Table created.
test@CRMD>insert into test select rownum,lpad('test',4000) from dba_source;
103602 rows created.
test@CRMD>commit;
Commit complete. 上面创建了一张拥有103602条记录的表。表的第一个字段唯一，第二个字段全部都是一样的、4K个字节。

CODE:

test@CRMD>create index t_1 on test(id,name);

Index created.

test@CRMD>create index t_2 on test(name,id);

Index created. 建立了两个索引。两个索引的建立方式，唯一的区别就是键值的顺序做了调换。
查看统计信息。

CODE:

test@CRMD>tabstat
Please enter Name of Table Owner: test
Please enter Table Name : test
Index                                      Leaf       Distinct         Number      AV      Av      Cluster Date
Name                           BLV         Blks           Keys        of Rows     LEA    Data       Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
T_1                              2      103,602        103,602        103,602       1       1      103,602 05-08-2012
T_2                             11      103,602        103,602        103,602       1       1      103,602 05-08-2012
Index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------------
T_1                            ID                                1 NUMBER(22)
                               NAME                              2 VARCHAR2(4000)
T_2                            NAME                              1 VARCHAR2(4000)
                               ID                                2 NUMBER(22) 发现两个索引的统计信息除了BLEVEL（上面的BLV)外，其他的信息完全一样。而且BLEVEL出现了“天壤之别”。
T_1的BLV为2。
T_2的BLV为12。

记得我刚开始做DBA的时候，老师告诉我一条准则，就是两个谓词如果都做等值查询，那么区分度好的要放在前面，也就是distinct值大的放前面。当时我并不知道为什么。
后面看LEWIS的COST成本算法。
按照走索引的COST的算法：
COST=索引成本+回表成本
索引成本=BLEVEL+BLOCKS*选择率
回表成本=聚簇因子*表的有效选择率

经过排除发现，貌似老师告诉我的准则是错误的，因为不管你建立的索引的键值顺序是怎么样的，索引成本的选择率和回表成本的表有效选择率都是维持不变的。索引叶子的BLOCKS是不变的。聚簇因子虽然有可能不一样，但是它完全依赖与索引相对与表的有序程度，与键值的区分度好坏完全没关系。
那么就差BLEVEL了。经过实验发现BLEVEL确实是不一样的。
为什么呢？

CODE:

test@CRMD>COL OBJECT_NAME FOR A20
test@CRMD>select object_name,object_id from dba_objects where object_name in ('T_1','T_2');

OBJECT_NAME OBJECT_ID
-------------------- ----------
T_1 10834
T_2 10835

test@CRMD>alter session set events 'immediate trace name treedump level 10834';


Session altered.

test@CRMD>alter session set events 'immediate trace name treedump level 10835';


Session altered. 上面的步骤可以把索引的内部结果DUMP出来。方便我们找到分支节点的物理位置。我们对叶子节点的物理结构不关心，因为两种情况下建立的索引，叶子节点数目是一样的，存储的内容也是一样的，只是键值顺序不一样。但是之所以第二种方式建立的索引，BLEVEL高，就是因为分支节点的数目大大增多的缘故，因此我们通过比对分支节点存储的内容，就能找到原因了。
查看DUMP文件：

CODE:

branch: 0x101980c 16881676 (0: nrow: 154, level: 2)--------------------------根节点地址
branch: 0x1019b03 16882435 (-1: nrow: 740, level: 1)-------------------------第一个分支节点的地址
leaf: 0x101980d 16881677 (-1: nrow: 1 rrow: 1)
leaf: 0x101980e 16881678 (0: nrow: 1 rrow: 1) 上面的DUMP内容是第一个索引的，我只截取了一小部分。第二个索引的DUMP内容我就不贴出来了。根据DUMP文件，我们DUMP出具体的分支块来看看里面具体的存储。

第一种方式建立的索引的分支块DUMP.

CODE:

row#0[8047] dba: 16882436=0x1019b04
col 0; len 3; (3): c2 08 2b
col 1; TERM
row#1[8038] dba: 16882437=0x1019b05
col 0; len 3; (3): c2 08 2c
col 1; TERM
row#2[8029] dba: 16882438=0x1019b06
col 0; len 3; (3): c2 08 2d
col 1; TERM 可以看到ORACLE只记录了第一列的数据在分支块里，第二列的键值根本没记录！！！！

第二种方式建立的索引分支块的DUMP.

CODE:

row#0[4046] dba: 16986126=0x103300e
col 0; len 4000; (4000):
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
省略非常长的内容
。
。
。
col 1; len 2; (2):  c1 03
col 2; TERM 第二种方式建立的索引两个列的内容都做了记录。

到这里基本就清楚了，ORACLE的分支块里，只记录必需的索引键值内容，也就是具有足够的区分度可以导航找到叶子块就可以了。
对于我们这个例子，对于第一种建立索引的方式，由于第一列的值已经是唯一的了，具有足够的区分度了，第二列就可以不在索引分支块里出现了。
而对于第二种建立索引的方式，由于第一列的值没有区分度，只有把第二列也加进去才具有区分度，因此就导致分支块里要把两个列的键值都加进去。
而我们的例子又比较的极端，第二列非常的大，有4K个字节，就导致分支块的数目非常的多，最终导致索引的BLEVEL非常的大，在我们的例子里
在一个只有103602行记录表的索引里，索引的层数就达到了12层。
看来当年老师告诉我的准则是有效的：当两个谓词只做等值查询，把区分度好的作为前导列建立索引比较好。但是可能这个准则并不是多么的重要，因为毕竟我们这里的例子太特殊了，第二个字段非常的长，而且不具有任何区分度，可能实际我们的环境中，效果差异并不那么大。

还有这个准则不是肯定有效的，因为聚簇因子的关系，如果字段A的顺序跟表的存储顺序极其一致，而字段B的极其不一致，但是字段A的区别度糟糕，字段B的区别度非常好。这个时候，到底是A,B好还是B,A好，要经过测试。

如果你充分理解了上面所说的内容，可以看下，如果我对表的数据做了如下的处理，那么两种方式建立的索引，还会有区别吗？

CODE:

test@CRMD>truncate table test;

Table truncated.
test@CRMD>insert /*+ append */into test select rownum,rownum||lpad('test',3993) from dba_source;

103614 rows created.
test@CRMD>commit;

Commit complete.
test@CRMD>create index t_1 on test(id,name);
test@CRMD>create index t_2 on test(name,id); 第二个字段，还是4K个字节，但是它的前导字段的区分度是用ROWNUM填充的，也就是说，第二个字段的前N个字符的区分度是唯一的。
在这种情况下，两种方式建立的索引，就是一样的，连分支块里存的内容也是一样的，ORACLE只截取了第二个字段的前N个（ROWNUM占的字节数）就可以了

CODE:

sys@CRMD>@tabstat

Index                                      Leaf       Distinct         Number      AV      Av      Cluster Date
Name                           BLV         Blks           Keys        of Rows     LEA    Data       Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
T_1                              2      103,614        103,614        103,614       1       1      103,614 05-09-2012
T_2                              2      103,614        103,614        103,614       1       1      103,614 05-09-2012
Index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------------
T_1                            ID                                1 NUMBER(22)
                               NAME                              2 VARCHAR2(4000)
T_2                            NAME                              1 VARCHAR2(4000)
                               ID                                2 NUMBER(22)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-723127/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-723127/