RelationalDBDesign RelationalDBDesign


Oracle Indexes   «Prev 

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.
  1. Rebuild the secondary index.
  2. 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 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.

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.

SELECT start_time, stop_time, FROM auction 
WHERE auction_id=7
The Oracle database would have to do this for all the rows in the table.

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.