ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何构建高BLEVEL的INDEX?

如何构建高BLEVEL的INDEX?

原创 Linux操作系统 作者:wei-xh 时间:2012-05-09 13:00:22 0 删除 编辑
[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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: SQL改写优化
请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2285635