Skip to Content

Difference between AOF Scan and Index Scan

The basic difference is how the table data is traversed by the query. With an INDEX SCAN, the table data is scanned in index order (an AOF may or may not be set on the table). An AOF SCAN means that the table is being scanned in natural record order (with an AOF set).

From a performance perspective, the AOF SCAN might be faster given an equal number of records to read. It is difficult to make such certain statements with regards to optimization.
The following are two specific statements where the first will show an AOF scan and the second an INDEX scan (assuming available indexes exist):

SELECT * from T where field = value;
SELECT * from T where field = value order by field2;

Both of those statements may result in setting an AOF. The first, though, results in scanning the table in index order via the AOF bitmap (if bits 1, 55, and 300 are set, it reads records 1, 55, and 300).
In the second one (assuming a temporary index is not built) it would set the same AOF but then scan the index so that it reads the records in order. That would typically cost more than the first statement.

No comments