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@]