Database Reference
In-Depth Information
•
Application domain indexes
: These are indexes you build and store yourself, either in Oracle
or perhaps even outside of Oracle. You tell the optimizer how selective your index is and how
costly it is to execute, and the optimizer will decide whether or not to use your index based
on that information. The Oracle text index is an example of an application domain index; it
is built using the same tools you may use to build your own index. It should be noted that
the index created here need not use a traditional index structure. The Oracle text index, for
example, uses a set of tables to implement its concept of an index.
As you can see, there are many index types to choose from. In the following sections, I'll present some technical
details on how each one works and when it should be used. I would like to stress again that we will not cover certain
DBA-related topics. For example, we will not discuss the mechanics of an online rebuild; rather, we will concentrate
on practical application-related details.
B*Tree Indexes
B*Tree—or what I call conventional—indexes are the most commonly used type of indexing structure in the database.
They are similar in implementation to a binary search tree. Their goal is to minimize the amount of time Oracle
spends searching for data. Loosely speaking, if you have an index on a number column, then the structure might
conceptually look like Figure
11-1
.
Figure 11-1.
Typical B*Tree index layout
