Oracle Indexes   «Prev  Next»

Lesson 2Why use indexes?
ObjectiveUnderstand the uses of indexes.

Oracle Database Indexes

An index is a structure in the Oracle database that contains a subset of the columns within a database table. The index is used to improve the performance of data retrieval.
An index is a transparent object within your Oracle database. An index is used to interact with data without intervention on the part of the user or the database administrator. If an index is transparent and the main storage of data is kept within the tables of the database, what is the use of an index?

Performance basics

The root of all data access performance is input/output operations. Data is read from and written to disk through the operation of a physical disk head interacting with a physical disk. The physical nature of this interaction makes I/O operations the largest potential bottleneck in your Oracle database. Indexes help reduce the amount of I/O involved in accessing data on disk. This helps reduce the effects of the physical I/O bottleneck.

How indexes help

Indexes help reduce I/O in the following way. Because an index contains a subset of the data in a table, a single disk read can get more index entries, so the overall number of I/O operations needed for access is smaller. This effect is enhanced when a selection condition is imposed on the values in an index.
Many indexes are stored in sorted order, which has two additional impacts on I/O performance. If a user query requests data in sorted order, it can be retrieved, pre-sorted, from an index. Because the index entries are in sorted order, it is also easier for Oracle to locate entries at random, as the database engine can use intelligence about the sorted order to get to a particular entry.

Oracle DBA

Retrieving data with an index

To better understand the impact of an index, consider the following two SlideShow which compares data access via an index to data access directly through an underlying table. Like many types of data access, this retrieval includes a selection condition.

Direct DataTable Access
Oracle Data Index
The next lesson explores the basic types of index structures.