The Internal Structure of Indexes【每日一译】--20121217

Oracle uses B-trees to store indexes to speed up data access. With no indexes, you have
to do a sequential scan on the data to find a value. For n rows, the average number of
rows searched is n/2. This does not scale very well as data volumes increase.
Consider an ordered list of the values divided into block-wide ranges (leaf blocks). The
end points of the ranges along with pointers to the blocks can be stored in a search tree
and a value in log(n) time for n entries could be found. This is the basic principle

behind Oracle indexes.

oracle使用B-TREES去存储索引去加快数据访问。如果没有索引，你必须顺序扫描数据去查找值。对于N行数据，平均

查找行数N/2。这对于大规模的数据增长不适合。

假定一个有序的值的单分成块宽度范围（叶结点）。范围的终点与块的指针可以存储在查找树中并且对于N个条目在LOG(n)次

后可以被发现。这是在ORACLE索引后的基本原理。

Figure 5–7 illustrates the structure of a B-tree index.

The upper blocks (branch blocks) of a B-tree index contain index data that points to
lower-level index blocks. The lowest level index blocks (leaf blocks) contain every
indexed data value and a corresponding rowid used to locate the actual row. The leaf
blocks are doubly linked. Indexes in columns containing character data are based on
the binary values of the characters in the database character set.
For a unique index, one rowid exists for each data value. For a nonunique index, the
rowid is included in the key in sorted order, so nonunique indexes are sorted by the
index key and rowid. Key values containing all nulls are not indexed, except for
cluster indexes. Two rows can both contain all nulls without violating a unique index.
Index Properties

上面的块（分支块）对于B-TREE索引包含索引数据，那个指向低一级索引的块。最低

级的索引块（叶结点）包含每个被索引化的数据值和一个对应的ROWID用于去定位实际

的行。叶结点块是双向链接的。在列中的索引包含字符数据是基于字符的二进制值在数据库的

字符集中。

对于唯一性索引，对于每一个数据值都有一个ROWID存在。对于一个非唯一性索引，ROWID是包含

在主键里面以排序顺序，所以非唯一索引排序是根据索引主键和ROWID。主键值包含所有的空值是不被

索引化的，除非是聚簇索引。两行都可以包含空而不违反唯一性索引。

索引特点

The two kinds of blocks:
■ Branch blocks for searching
■ Leaf blocks that store the values
Branch Blocks Branch blocks store the following:
■ The minimum key prefix needed to make a branching decision between two keys
■ The pointer to the child block containing the key
If the blocks have n keys then they have n+1 pointers. The number of keys and
pointers is limited by the block size.
Leaf Blocks All leaf blocks are at the same depth from the root branch block. Leaf
blocks store the following:
■ The complete key value for every row
■ ROWIDs of the table rows
All key and ROWID pairs are linked to their left and right siblings. They are sorted by
(key, ROWID).

两种类型的数据块：

#对于查找的分去块

#存储值的叶结点块

分去块存储如下内容：

#使最小主键前缀需要去使一个分支取决于两个键之间

#对于子块的指针包含着主键值

如果块拥有N个主键那么它们拥有N+1个指针。主键的数量和指针都受限于数据块的大小。

叶块所有的叶块都有一个相同的深度从ROOT分去块。叶块存储着以下的内容：

#对于每行的完全的主键值

#表行的ROWID值

所有的主键和ROWID成对的链接它们左边和右边的兄妹块。它们排序是按（KEY和ROWID）