Lesson 2	Why use indexes?
Objective	Understand 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 and 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 based on 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.

1) The select condition calls for finding a row in the AUCTION table with a particular value for the AUCTION_ID column. In this version of the database, there is no index on the AUCTION_ID column. The rows appear in the table in the order they have been inserted, not in any sorted order.

2) If Oracle were to directly read the database, it would have to read a row and compare the value of the AUCTION_ID column with the selection value.

3) Table access — 3) The Oracle database would have to do this for all the rows in the table.

SELECT start_time, stop_time, FROM auction WHERE auction_id=7

4) Although the mutiple rows would be read in a single I/O operation, this type of access would still require a large amount of disks activity.

Data Access Directly to a Table

You can get information from the data dictionary to determine if the physical guesses for an ( IOT) index organized table are stale by querying the PCT_DIRECT_ACCESS column of USER_INDEXES. For example,

SQL> select index_name, index_type, pct_direct_access
2 from user_indexes;
INDEX_NAME INDEX_TYPE PCT_DIRECT_ACCESS
------------------------------ --------------------------- -----------------
EMPLOYEES_IOT_PK IOT - TOP 0
EMPLOYEES_PART_1I NORMAL 100

If the PCT_DIRECT_ACCESS value falls below 100, it means the secondary index entries are becoming migrated, and the physical guess can start to be inaccurate enough that extra I/O operations will start occurring and performance will start to degrade. Once the PCT_DIRECT_ACCESS falls below 80, performance degradation will start becoming more noticeable and the index may be a good candidate for a rebuild operation. In order to refresh the logical ROWIDs over time, there are two primary ways to address the issue.

Rebuild the secondary index.
Update the block references for the index.

The first way to refresh the logical ROWIDs within secondary indexes is simply by rebuilding the index(es). Rebuilding secondary indexes built on index-organized tables is no different than rebuilding indexes on heap organized tables.
The following link takes you to a section which discusses Oracle Data Index
The next lesson explores the basic types of index structures.