INDEX_DESC

The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values. Syntax of the INDEX_DESC hint is

Each parameter serves the same purpose as in the INDEX hint. This hint has no effect on SQL statements that access more than one table. Such statements always perform range scans in ascending order of the indexed values. For example, consider this table, which contains the temperature readings of a tank of water holding marine life:

CREATE TABLE tank_readings

   (time         DATE    CONSTRAINT un_time UNIQUE,

    temperature  NUMBER );

Each of the table's rows stores a time and the temperature measured at that time. A UNIQUE constraint on the TIME column ensures that the table does not contain more than one reading for the same time.

Oracle enforces this constraint with an index on the TIME column. Consider this complex query, which selects the most recent temperature reading taken as of a particular time T. The subquery returns either T or the latest time before T at which a temperature reading was taken. The parent query then finds the temperature taken at that time:

SELECT temperature

   FROM tank_readings

   WHERE time = (SELECT MAX(time)

      FROM tank_readings

         WHERE time <= TO_DATE(:t) );

The execution plan for this statement looks like the following figure:

Figure 7-1: Execution Plan without Hints

To execute this statement, Oracle performs these operations:

• Steps 4 and 3 execute the subquery:
  • Step 4 performs a range scan of the UN_TIME index to return all the TIME values less than or equal to T.
  • Step 3 chooses the greatest TIME value from Step 4 and returns it.
• Steps 2 and 1 execute the parent query:
  • Step 2 performs a unique scan of the UN_TIME index based on the TIME value returned by Step 3 and returns the associated ROWID.
  • Step 1 accesses the TANK_READINGS table using the ROWID returned by Step 2 and returns the TEMPERATURE value.

In Step 4, Oracle scans the TIME values in the index in ascending order beginning with the smallest. Oracle stops scanning at the first TIME value greater than T and then returns all the values less than or equal to T to Step 3. Note that Step 3 needs only the greatest of these values. Using the INDEX_DESC hint, you can write an equivalent query that reads only one TIME value from the index:

SELECT  temperature

   FROM tank_readings

   WHERE time <= TO_DATE(:t)

      AND ROWNUM = 1

   ORDER BY time DESC;

The execution plan for this query looks like the following figure:

Figure 7-2: Execution Plan Using the INDEX_DESC Hint

To execute this statement, Oracle performs these operations:

• Step 3 performs a range scan of the UN_TIME index searching for TIME values less than or equal to T and returns their associated ROWIDs.
• Step 2 accesses the TANK_READINGS table by the ROWIDs returned by Step 3.
• Step 1 enforces the ROWNUM=1 condition by requesting only one row from Step 2.

Because of the INDEX_DESC hint, Step 3 scans the TIME values in the index in descending order beginning at T. The first TIME value scanned is either T (if the temperature was taken at T) or the greatest TIME value less than T. Since Step 1 requests only one row, Step 3 scans no more index entries after the first.Since the default behavior is an ascending index scan, issuing this query without the INDEX_DESC hint would cause Oracle to begin scanning at the earliest time in the table, rather than at the latest time less than or equal to T. Step 1 would then return the temperature at the earliest time. You must use this hint to make this query return the same temperature as the complex query described earlier in this section.