INDEX (FULL SCAN DESCENDING)

Description

Perform a descending index full scan

Versions

This operation is implemented in the following versions

7.3.4
8.0.4
8.0.5
8.0.6
8.1.5
8.1.6
8.1.7
9.0.1
9.2.0
10.1.0
10.2.0

Example

Example 1

In this example the indexed columns must have a NOT NULL constraint

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table definition 

    CREATE TABLE t1 (c1 NUMBER NOT NULL,c2 NUMBER);

    CREATE INDEX i1 ON t1 (c1);

The table does not need to be analysed

The statement

    SELECT /*+ INDEX_DESC (t1 i1) */ c1
    FROM t1;

generates the following execution plan 

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   INDEX (FULL SCAN DESCENDING) OF 'I1' (NON-UNIQUE)
Example 2

This example demonstrates that if an IS NOT NULL predicate is used in the WHERE clause, a NOT NULL constraint is not required

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table definition

    CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);

    CREATE INDEX i1 ON t1 (c1);

The table does not need to be analysed

The statement 

    SELECT /*+ INDEX_DESC (t1 i1) */ c1
    FROM t1
    WHERE c1 IS NOT NULL;

generates the following execution plan 

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   INDEX (FULL SCAN DESCENDING) OF 'I1' (NON-UNIQUE)