The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

LinkLinkedIn

INDEX_ASC AND INDEX_DESC

Index_Asc Hint Description

The INDEX_ASC hint forces Oracle to use an index scan for the specified table. If the statement uses an index range scan, then Oracle Database scans the index entries in ascending order of their indexed values. Each parameter serves the same purpose as in INDEX Hint.

IMPORTANT
The default behavior for a range scan is to scan index entries in ascending order of their indexed values, or in descending order for a descending index. This hint does not change the default order of the index, and therefore does not specify anything more than the INDEX hint. However, you can use the INDEX_ASC hint to specify ascending range scans explicitly should the default behavior change.

Index_Desc Hint Description

The INDEX_DESC hint forces Oracle to use a descending index scan for the specified table. If the statement uses an index range scan and the index is ascending, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.

Important
For a descending index, this hint effectively cancels out the descending order, resulting in a scan of the index entries in ascending order. Each parameter serves the same purpose as in INDEX Hint.

Example

SELECT /*+ INDEX_DESC(a myInd1) */ *

  FROM myTab1 a

/

Published on 

Published 24th May 2022