How Indexes Are Stored

　reference:  http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#CHDJGADJ

 

　　当创建索引的时候，Oracle 会自动在表空间中分配一个索引段来存储索引数据。你可以控制索引段的空间分配并且通过以下两种方式对这部分保留空间进行使用：

• 通过为索引段设置存储参数来控制索引段空间的分配范围。
• 通过为索引段设置 PCTFREE 参数来控制组成索引段范围中的空闲空间。

 

The section includes the following topics:

• Format of Index Blocks
• The Internal Structure of Indexes
• Index Properties
• Advantages of B-tree Structure

Format of Index Blocks

索引数据的空用空间大小为 Oracle Database block size - block overhead, entry overhead, rowid and one length byte for each value indexed.

当你创建一个索引的时候，Oracle Database 获取被索引的数据列，并对它们进行排序，然后根据每行的索引值对rowid进行存储。然后Oracle Database 自底向上对索引进行加载。例如： 

CREATE INDEX employees_last_name ON employees(last_name);

Oracle 根据last_name对表 employees 进行排序，然后按顺序加载由 last_name 和相应的rowid组成的索引。当使用索引的时候，Oracle对有序的 last_name 做一次快速查询，然后使用相关联的 rowid 的值去定位含有last_name值的所有行。

 

The Internal Structure of Indexes

Oracle 使用 B-trees 存储索引来加速数据访问。如果没有索引，你必须通过对数据进行 sequential scan 来找到目标值。在 n 行中进行查找，平均搜索的行数为 n/2。当数据量增加时，这种方式是很不合适的。

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 Database indexes.

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

Figure 5-7 Internal 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

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).

Advantages of B-tree Structure

The B-tree structure has the following advantages:

• All leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the index takes approximately the same amount of time.

• B-tree indexes automatically stay balanced.

• All blocks of the B-tree are three-quarters full on the average.

• B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.

• Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.

• B-tree performance is good for both small and large tables and does not degrade as the size of a table grows.