Database Reference
In-Depth Information
Chapter 11
Indexes
Indexing is a crucial aspect of your application design and development. Too many indexes and the performance of
modifications (inserts, updates, merges, and deletes) will suffer. Too few indexes and the performance of DML
(including select, inserts, updates, and deletes) will suffer. Finding the right mix is critical to your application's performance.
Frequently, I find that indexes are an afterthought in application development. I believe that this is the wrong
approach. If you understand how the data will be used from the very beginning of the process, you should be able to
come up with the representative set of indexes you will use in your application. Too often the approach seems to be to
throw the application out there and then see where indexes are needed. This implies that you have not taken the time
to understand how the data will be used and how many rows you will ultimately require. You'll be adding indexes to
this system forever as the volume of data grows over time (i.e., you'll perform
reactive tuning
). You'll have indexes that
are redundant and never used; this wastes not only space but also computing resources. A few hours at the start spent
properly considering when and how to index your data will save you many hours of tuning further down the road
(note that I said doing so
will
, not
might
, save you many hours).
The basic aim of this chapter is to give an overview of the indexes available for use in Oracle and discuss
when and where you might use them. This chapter differs from others in this topic in terms of its style and format.
Indexing is a huge topic—you could write an entire book on the subject—in part because indexing bridges the
developer and DBA roles. The developer must be aware of indexes, how indexes apply to their applications, when
to use indexes (and when not to use them), and so on. The DBA is concerned with the growth of an index, the use of
storage within an index, other physical properties, and the overall performance of the database. We will be tackling
indexes mainly from the standpoint of their practical use in applications. The first half of this chapter conveys the
basic knowledge I believe you need to make intelligent choices about when to index and what type of index to use.
The second half of the chapter answers some of the most frequently asked questions about indexes.
The various examples in this chapter require different feature releases of Oracle. When a specific example
requires features found in Oracle Enterprise or Personal Edition but not Standard Edition, I'll specify that.
An Overview of Oracle Indexes
Oracle provides many different types of indexes for us to use. Briefly, they are as follows:
•
B*Tree indexes
: These are what I refer to as
conventional
indexes. They are, by far, the most
common indexes in use in Oracle and most other databases. Similar in construct to a binary
tree, B*Tree indexes provide fast access, by key, to an individual row or range of rows, normally
requiring few reads to find the correct row. It is important to note, however, that the “B” in “B*Tree”
does not stand for
binary
but rather for
balanced
. A B*Tree index is not a binary tree at all, as we'll
see when we look at how one is physically stored on disk. The B*Tree index has several subtypes:
•
Index organized tables
: These are tables stored in a B*Tree structure. Whereas rows of
data in a heap table are stored in an unorganized fashion (data goes wherever there
is available space), data in an IOT is stored and sorted by primary key. IOTs behave
