Re: When does Oracle use 'Index Fast Scan'

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

• Dave

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.
>
>
>
> ryan.gaffuri_at_cox.net 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: http://www.orafaq.net
-- 
Author: David Hau
  INET: davehau123_at_netscape.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).