Databases Reference
In-Depth Information
specify the leading column of a composite index (as in previous releases of the database—that is, pre
Oracle 9i), the database uses a skip scan of the composite index in this case.
Index Full Scan
An index full scan is the operation of reading
all
the entries in a given index. In that sense, a full index
scan is analogous to a full table scan. A full index scan is a good alternative to doing a full table scan first
and sorting the data afterwards. Oracle Database is likely to use a full index scan in any of the following
conditions:
•
A query requires a sort merge join
: All columns referenced by the query must exist
in the index and the order of the leading index columns must be identical to the
columns specified in the query.
The query contains an
ORDER BY
clause
: All the columns in the clause must be
present in the index.
•
•
The query contains a
GROUP BY
clause
: The index and the
GROUP BY
clause must
contain the same columns, although not necessarily in the same order.
The following is an example that shows how the database utilizes an index full scan operation to
retrieve the data without performing a sort. The full index scan avoids a sort operation because the index
is already sorted. Full index scans read single data blocks and don't perform a multiblock read operation.
SQL> select * from test order by a;
Execution Plan
----------------------------------------------------------
Plan hash value: 3311708430
---------------------------------------------------------------------------
| Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
---------------------------------------------------------------------------
| 0|SELECT STATEMENT | |9433|1040K| 79 (0)|00:00:03|
| 1| TABLE ACCESS BY INDEX ROWID|TEST |9433|1040K| 79 (0)|00:00:03|
| 2| INDEX FULL SCAN |TEST_PK1|9433| | 21 (0)|00:00:01|
---------------------------------------------------------------------------
SQL>
In this query, the database performs an index full scan first and then performs a table access by index
ROWID
operation. This is so because the query requests columns besides the indexed column (
SELECT *
FROM …).
However, if a query requests just the indexed column along with an
ORDER BY
clause, the
database skips the table access and gets the data back by accessing the index alone, without having to
read the table values.
Index Fast Full Scan
Oracle Database performs an index fast full scan as an alternative to a full table scan, when the index
itself contains all the columns that you specify in the query. In the following example, notice that there's
only an index fast full scan operation to retrieve the data and that the table itself isn't accessed at all:
