Databases Reference
In-Depth Information
CHAPTER 2
■ ■ ■
B-tree Indexes
The B-tree index is the default index type in Oracle. This index type is known as B-tree because the table
row identifier (
ROWID
) and associated column values are stored within index blocks in a
balanced
tree-
like structure. Oracle B-tree indexes are used for the following reasons:
•
Improving SQL statement performance.
•
Enforcing uniqueness of primary key and unique key constraints.
•
Reducing potential locking issues with parent and child table tables associated via
primary and foreign key constraints.
If a table column value (or combination of columns) is fairly unique within all rows in a table, then
creating a B-tree index usually results in faster query performance. Additional performance
improvements are realized when the index structure itself contains the required table column values to
satisfy the result of the query. In this situation, the table data blocks need not be accessed.
Understanding these concepts will help you determine which columns to index and whether a
concatenated index might be more efficient for certain queries and less optimal for others.
B-tree indexes also play a pivotal role in application design because these indexes are closely
associated with certain types of constraints. Namely, Oracle uses B-tree indexes to enforce primary key
and unique key constraints. In most scenarios, B-tree indexes are automatically created for you when
implementing primary key and unique key constraints.
Indexes are often manually created to match foreign key constraint columns to improve
performance of queries that join tables on primary key and foreign key columns. Also, in certain
circumstances, the lack of a B-tree index on a foreign key column(s) can cause locking problems.
We start this chapter by describing how Oracle uses B-tree indexes. Then we cover typical strategies
for implementing and managing B-tree indexes. The last major section of this chapter deals with B-tree
indexes and how they are related to constraints.
Understanding How Oracle Uses B-tree Indexes
This section will help you understand how Oracle uses B-tree indexes. The goal is to help you fully
comprehend B-tree index internals to enable intelligent indexing decisions when building database
applications. An example with a good diagram will help illustrate the mechanics of a B-tree index. Even
if you've been working with B-tree indexes for quite a while, this example may illuminate technical
aspects of using an index. To get started, suppose you have a table created as follows:
