Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: When does Oracle use 'Index Fast Scan'

Re: When does Oracle use 'Index Fast Scan'

From: David Hau <>
Date: Mon, 26 Jan 2004 19:34:25 -0800
Message-ID: <>

Correction: the Index Range Scan can be parallelized when it involves multiple partitions.

David Hau wrote:

> I assume you're talking about the Fast Full Index Scan. This is used
> when the index contains all the columns necessary to answer the query.
> It's faster than a Full Table Scan because indexes are smaller than
> entire rows, so a Fast Full Index Scan will scan fewer blocks than a
> Full Table Scan.
> It's faster than an Index Range Scan firstly because Fast Full Index
> Scan scans the blocks in sequential order, whereas the Index Range
> Scan traverses the B-tree index structure in scanning the blocks,
> resulting in a random access I/O pattern which is slower. This is
> also why the Oracle documentation says that with a Fast Full Index
> Scan, the result is not sorted by the index key (because the result is
> not obtained by traversing the index structure.) Secondly, the better
> performance is also because the Fast Full Index Scan uses multiblock
> reads and is capable of parallel operation, whereas the Index Range
> Scan is capable of neither.
> Regards,
> Dave.
> wrote:
>> I have found that the vast majority of time that Oracle chooses this
>> method, my statistics are stale and the query is sub-optimal. One
>> time, Oracle changed from a 'range scan' to this type of scan with a
>> FIRST_ROWS hint and this reduced performance.
>> This is just a full scan of the index, one block at a time right?
>> When would this ever be superior to a Fast Full Scan or a Range Scan?

Please see the official ORACLE-L FAQ:
Author: David Hau

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Jan 26 2004 - 21:34:25 CST

Original text of this message