oracle复合索引的组织

magic007

原帖由 yourslg 于 2009-9-11 17:10 发表
刚才看见一篇文章说多列索引键值的存放是oracle自己判断存放哪些列值，判断的标准是这个值只需要能区分其左右指针所指向的两个block的值就可以了，若一个列值不能区分则增加一个可以区分的值为止（Oracle官方未对此明确介绍）。

周末回去试验看看结果是咋样。谢谢zergduan  的提示。

对于branch block，oracle只需要区分其指针的值就可以了，这里面有两个含义，1，可能只需要多列中的部分前导列就可以了，2，存储的索引的最后一列可能只需要那一列的前面部分字节。

对于leaf block来说，需要存储每一列的完整的值。

Yong Huang

原帖由 yourslg 于 2009-9-11 03:10 发表
刚才看见一篇文章说...

As soon as you say that, you need to tell us the URL or book title (and page number).

Yong Huang

〇〇

关注 学习

yourslg

我看的那篇文章地址：http://q.yesky.com/group/review-11028252.html

eagle_fan

原帖由 yourslg 于 2009-9-17 08:40 发表
我看的那篇文章地址：http://q.yesky.com/group/review-11028252.html

这是我以前写的 ，itpub上贴过

http://www.itpub.net/viewthread.php?tid=727474

Yong Huang

原帖由 eagle_fan 于 2009-9-24 01:08 发表

这是我以前写的:) ，itpub上贴过

http://www.itpub.net/viewthread.php?tid=727474

Thank you. The URL in message #14 points at a plagiarized article. Plagiarism is a big problem on Chinese web sites. I'm glad we have little or none in this forum.

Richard Foote in his "Oracle B-Tree Index Internals" (http://richardfoote.files.wordpr ... lding-the-truth.pdf) says

"column value [in a branch block] is abbreviated to smallest value that uniquely defines path"

I have a feeling that your conclusion can be expanded to say that if part of the first column is enough to distinguish between two leaf nodes, then the branch block doesn't even need to store the first column fully; it can just store that first part of the column. That is, if the leaf blocks are like

('aaaaa', 'name1')  -- in 1st leaf
('baaaa', 'name1')  -- in 2nd leaf
('caaaa', 'name1')   -- in 3rd leaf

then I'm guessing the branch block above them can just store the first letter of the first column.

Yong Huang

eagle_fan

That's true.