讨论：db2得索引

mdkii

07年的时候狼还在苦苦修炼九阳神功，因此还会贴些讨论贴上来，互相切磋一下。
现在估计九阳神功早就大功告成了，已经睥睨天下，傲视群雄了，呵呵。

diablo2

今天突然想到，DB2是怎么在INDEX里处理NULL的？

mdkii

原帖由 mdkii 于 2010-1-27 17:26 发表
07年的时候狼还在苦苦修炼九阳神功，因此还会贴些讨论贴上来，互相切磋一下。
现在估计九阳神功早就大功告成了，已经睥睨天下，傲视群雄了，呵呵。

这是我写的吗？！

wangzhonnew

原帖由 diablo2 于 2010-9-28 17:14 发表
今天突然想到，DB2是怎么在INDEX里处理NULL的？

唉，回首07年往事，问得问题简直都是可笑的冬冬……

大波罗的问题：
比如我创建一个table
create table t1 (c1 int)
create index i1 on t1 (c1)
然后插入1,2,NULL
然后看index，我们有
     0000  *3000D01F 02000000 0005010A 03000000*     *0...............*
     0010  *C2020000 05000000 00000000 35000000*     *............5...*
     0020  *F5E4F102 00000000 00000000 00000000*     *................*
     0030  *F501341B 0D021041 00000000 5E1BCC04*     *..4....A........*
     0040  *02000000 01000000 00000000 00000000*     *................*
     0050  *00000000 58005254 31300000 0E000500*     *....X.RT10......*
     0060  *00000000 00000A00 00000000 A5D4F102*     *................*
     0070  *00000000 00000000 00000000 00000000*     *................*
     0080  *00000000 00000000 00000000 01430040*     *.............C..*
     0090  *00000000 00000002 FF00FFFF 01000100*     *................*
     00A0  *00000000 00000100 04000100 F6030000*     *................*
     00B0  *00000300 00000300 A204B004 BE040000*     *................*
     00C0  *00000000 00000000 00000000 00000000*     *................*
     00D0  *00000000 00000000 00000000 00000000*     *................*
     00E0  *00000000 00000000 00000000 00000000*     *................*
     00F0  *00000000 00000000 00000000 00000000*     *................*
然后在相对应得地方我们有
     04A0  *00000E00 00010000 00040000 00000002*     *................*
     04B0  *0E000002 00000005 00000000 00020E00*     *................*
     04C0  *01000000 00060000 00000002 00000000*     *................*
     04D0  *00000000 00000000 00000000 00000000*     *................*
     04E0  *00000000 00000000 00000000 00000000*     *................*
     04F0  *00000000 00000000 00000000 00000000*     *................*
     0500  *00000000 00000000 00000000 00000000*     *................*

这里，红绿黄各对应3个key，分别是1,2,NULL，对应的RID在棕色部分标出
每一个index key都是2字节长度+1字节NULL+Key+RIDList表示。如果NULL部分设置为1，也就是TRUE，就代表这个是NULL。
NULL key所在的位置是索引的末尾

[ 本帖最后由 wangzhonnew 于 2010-9-28 21:13 编辑 ]

sdusun

原帖由 diablo2 于 2010-9-28 16:14 发表
今天突然想到，DB2是怎么在INDEX里处理NULL的？

INDEX应该处理不了Null，换言之，如果某列有Null值，下面的SQL很可能不会走索引

select ... from tab where col1 is not null;

wangzhonnew

原帖由 sdusun 于 2010-9-29 11:11 发表

INDEX应该处理不了Null，换言之，如果某列有Null值，下面的SQL很可能不会走索引

select ... from tab where col1 is not null;

"is not null" might not be able to use it, because it has to scan the entire index tree and only skip the keys that's not null, so it's sargable index scan...
but if "NULL" is not majority in a big table, if the query is "IS NULL", it should be able to use IXSCAN

mdkii

好东东，学习了。
我在V9.1上也试了一下，但貌似索引的结构跟这个不一样：
首先建表建索引
create table test(c1);
create index on test(c1 int)
insert into test values(1,2,null);

然后用db2dart生成ascii data和raw data
（参考 wangzhonnew 的帖子 http://www.itpub.net/viewthread.php?tid=1236181 ）

ascii data（只截取了相关的部分）：

         Key 0:

            Offset Location = 648  (x288)

            Record Length   = 14  (xE)

            Key Part 1:
                Long Integer
                Value = 1
            Table RID: x(0000 6F40 0004) r(00006F40;0004) d(28480;4) ridFlags=x0

         Key 1:

            Offset Location = 662  (x296)

            Record Length   = 14  (xE)

            Key Part 1:
                Long Integer
                Value = 2
            Table RID: x(0000 6F40 0005) r(00006F40;0005) d(28480;5) ridFlags=x0

         Key 2:

            Offset Location = 676  (x2A4)

            Record Length   = 14  (xE)

            Key Part 1:
                Long Integer
                Value = NULL
            Table RID: x(0000 6F40 0006) r(00006F40;0006) d(28480;6) ridFlags=x0

      Total Record bytes on this page = 42


   Index object report phase end.
   
   
raw data（只截取了相关的部分）：

0270  *00000000 00000000 00000000 00000000*     *................*
0280  *00000000 00000000 000E0000 00000100*     *................*
0290  *006F4000 0400000E 00000000 0200006F*     *.o.............o*
02A0  *40000500 000E0100 00000000 006F4000*     *.............o..*
02B0  *06000000 00000000 00000000 00000000*     *................*
02C0  *00000000 00000000 00000000 00000000*     *................*

对比一下：
先看key0，它的位置在x288，长度为xE（14）
那么就是这一段:
000E0000 00000100 006F4000 0400
分解开看：
000E表示长度
00 不知道干嘛，但看后面（key2）貌似表示是否为null
00000001 四个字节的整数，就是key值
00006F400004 这个就应该是RID了吧。
00 对照ridFlags=x0，应该是个标志，不知道干嘛的。
key1，key2也可以类似对照分析。


我们再插入一条记录试试：
insert into t1 values(1);

dump出的结果如下：
ascii:
Key 0:

            Offset Location = 690  (x2B2)

            Record Length   = 21  (x15)

            Key Part 1:
                Long Integer
                Value = 1
            Table RID: x(0000 6F40 0004) r(00006F40;0004) d(28480;4) ridFlags=x0
            Table RID: x(0000 6F40 0007) r(00006F40;0007) d(28480;7) ridFlags=x2 Punc
            
raw:
        0280  *00000000 00000000 000E0000 00000100*     *................*
        0290  *006F4000 0400000E 00000000 0200006F*     *.o.............o*
        02A0  *40000500 000E0100 00000000 006F4000*     *.............o..*
        02B0  *06000015 00000000 0100006F 40000400*     *...........o....*
        02C0  *00006F40 00070200 00000000 00000000*     *..o.............*
        02D0  *00000000 00000000 00000000 00000000*     *................*
        
        
从数据看，DB2在后面增加了一项：
并把所有的rid窜了起来，
offset也变为690，原来那一项（offset 648 绿色部分）貌似废弃不用了（浪费空间？）
0015 00000000 0100006F 40000400 00006F40 000702，
注意这里的ridflag变为了x2（橙色部分）。

后来我多插了几条记录，验证了这个结论。
由此可见，在insert重复key的记录时，索引维护的开销貌似挺大的，
DB2会把之前的索引项复制一遍，
加入新的rid，然后形成一个新的索引项写到磁盘中。

我再试着做了一个reorg index，
reorg indexes all for table tt。
果然，那些废弃的索引项都消失了。

wp28556259

mdkii 发表于 2010-9-29 12:07
好东东，学习了。
我在V9.1上也试了一下，但貌似索引的结构跟这个不一样：
首先建表建索引

offset也变为690，原来那一项（offset 648 绿色部分）貌似废弃不用了（浪费空间？）
怎么看出来不用了呢？那条记录还是在RAW里面呀？？

mdkii

废弃不用了是说，虽然还在，但DB2不会用它了，
因为 offset变为 690了。

wp28556259

mdkii 发表于 2011-9-27 11:25
废弃不用了是说，虽然还在，但DB2不会用它了，
因为 offset变为 690了。

那原来OFFSET为648的数据还有KEY指向它吗？