Databases Reference
In-Depth Information
CHAPTER 1
■ ■ ■
Oracle Indexes
An index is an optionally created database object used primarily to increase query performance. The
purpose of a database index is similar to an index in the back of a topic. A book index associates a topic
with a page number. When you're locating information in a topic, it's usually much faster to examine the
index first, find the topic of interest, and identify associated page numbers. With this information, you
can navigate directly to specific page numbers in the topic. If the topic only appears on a few pages
within the topic, then the number of pages to read is minimal. In this manner, the usefulness of the
index decreases with an increase in the number of times a topic appears in a topic.
Similar to a topic index, a database index stores the column value of interest along with its row
identifier (
ROWID
). The
ROWID
contains the physical location of the table row on disk that stores the
column value. With the
ROWID
in hand, Oracle can efficiently retrieve table data with a minimum of disk
reads. In this way, indexes function like a shortcut to the table data. If there is no available index, then
Oracle reads each row in the table to determine if the row contains the desired information.
Note
In addition to improving performance, Oracle uses indexes to help enforce enabled primary key and
unique key constraints. Additionally, Oracle can better manage table locking scenarios when indexes are placed on
foreign key columns.
While it's possible to build a database application devoid of indexes, without them you're almost
guaranteeing poor performance. Indexes allow for excellent scalability even with very large data sets. So
if indexes are so important to database performance, why not place them on all tables and column
combinations? The answer is short: indexes are not free. They consume disk space and system resources.
As column values are modified, any corresponding indexes must also be updated. In this way, indexes
use storage, I/O, CPU, and memory resources. A poor choice of indexes leads to wasted disk usage and
excessive consumption of system resources. This results in a decrease in database performance.
For these reasons, when you design and build an Oracle database application, expert consideration
must be given to your indexing strategy. As an application architect, you must understand the physical
properties of an index, what types of indexes are available, and strategies for choosing which table and
column combinations to index. A correct indexing methodology is central to achieving maximum
performance for your database.
This chapter introduces you to Oracle indexing concepts. We begin with a to-the-point example of
how an index improves query performance. We then explain index types available within Oracle and
