Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
B-tree index
- Thread starter anandcv
- Start date
Hi,
Oracle version 8 provides five indexing schemes: 1.B*tree indexes
2.B*tree cluster indexes
3.hash cluster indexes
4.reverse key indexes
5.bitmap indexes
Oracle uses a balanced B-Tree (Binary Search Tree) structure to build indexes. The upper blocks (branch blocks) of a balanced 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, there is one rowid per 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.
The balanced 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.
Leaf block splits occur when an index B-Tree structure needs to be re-balanced. These occur during inserts, updates and deletes to the associated table.
You may also check Note:30405.1 at Metalink for further details.
HTH
Regards
Himanshu
Oracle version 8 provides five indexing schemes: 1.B*tree indexes
2.B*tree cluster indexes
3.hash cluster indexes
4.reverse key indexes
5.bitmap indexes
Oracle uses a balanced B-Tree (Binary Search Tree) structure to build indexes. The upper blocks (branch blocks) of a balanced 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, there is one rowid per 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.
The balanced 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.
Leaf block splits occur when an index B-Tree structure needs to be re-balanced. These occur during inserts, updates and deletes to the associated table.
You may also check Note:30405.1 at Metalink for further details.
HTH
Regards
Himanshu
- Status
Similar threads
- Locked
- Question
- Locked
- Question