The Internal Structure of Indexes (208)

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.

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.

索引的内部结构
1. Oracle 索引的基本原理 : 如果将一个已排序的值列划分为以块为单位的区间，每个区间的末尾包含
指向下个区间的指针，而搜索树中则保存指向每个区间的指针。此时在 n 行数据中查询一个值所需的时间为
log(n)。
2. B树索引的分支块包含了指向下层索引块的指针 ，叶子块包含了被索引的数据值，以及对应的 rowid 。
叶子节点以双向列表形式连接
3. 对于唯一索引 ，每个索引值对应着唯一的一个 rowid。对于非唯一索引，每个索引值
对应着多个已排序的 rowid。因此在非唯一索引中，索引数据是按照索引键及 rowid 共同排序的。
键值全部为 NULL 的行不会被索引，只有簇索引例外。

[@more@]

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

转载于:http://blog.itpub.net/10599713/viewspace-982565/