INDEX (FULL SCAN)

Description

Perform an index full scan

Versions

This operation is implemented in the following versions

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 (t1 i1) */ c1
    FROM t1;

generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   INDEX (FULL SCAN) 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 (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) OF 'I1' (NON-UNIQUE)