High Volume Index Scans

If your high volume SQL is using an index, it may be a problem. There are 6 main types of index scan; in Explain Plan you will see one of the following:

[UNIQUE] INDEX RANGE SCAN

Oracle is reading 0 or more contiguous rows from the index.

[UNIQUE] INDEX RANGE SCAN (MIN/MAX)

Oracle is identifying 0 or more contiguous rows in the index, but is reading only one (the first or the last) in order to satisy a MIN or MAX aggregate function.

UNIQUE INDEX UNIQUE SCAN

Oracle is reading 0 or 1 rows from the index.

[UNIQUE] INDEX FULL SCAN

Oracle is reading all rows from the index, and may be accessing these rows in the underlying table.

[UNIQUE] INDEX SKIP SCAN

Oracle is reading 0 or more rows from different parts of the index, and may be accessing these rows in the underlying table.

[UNIQUE] INDEX FAST FULL SCAN

Oracle is reading all rows from the index, and is not accessing these rows in the underlying table. ie. The index contains all columns required to resolve the query without having to lookup the table.


In a high volume SQL, the last index scan type above is not a problem. With the first five types, they are usually only a problem if Oracle is also performing a table access along with the index scan. The table access will appear in your Explain Plan immediately above the index scan. eg:

If your SQL is not performing a table access, but the index uses a RANGE SCAN (but not a RANGE SCAN MIN/MAX), you could still have a Range Scan problem, where the range scan is processing a lot more rows than you expect. Otherwise, an index scan that does nore have a TABLE ACCESS is not necessarily a problem; try performing a full table scan instead and compare the performance.

Does the full table scan perform better? If not, then one of the following will apply:


©Copyright 2003