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?
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.
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.
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.