Re: When does Oracle use 'Index Fast Scan'

Another situation where index full scans might be handy, would be where hash joins are disabled and sorted output can be used for "fast" sort-merge join.

Btw, multiblock reads are available for regular index range and full scan under some specific conditions as well - I'm talking about readahead and parameter _non_contiguous_multiblock_read for example. However, in my brief tests I've not managed to see this kind of behaviour yet.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, January 27, 2004 6:04 PM

> One situation I can think of where a (non-fast) full index scan can be
> helpful is when the index contains all the columns needed for the query,
> the query requires all the rows of the table, and the query requires the
> results to be sorted according to the index. This way, fast full index
> scan may be slower because you need to sort the rows after retrieving
> the blocks, whereas the non-fast full index scan does not.
>
> Regards,
> Dave
>
>
>
> ryan.gaffuri_at_cox.net wrote:
>
> > I know when oracle uses a fast full scan. Its the full scan that does 1
I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed.
> >
> >
> >>From: David Hau <davehau123_at_netscape.net>
> >>Date: 2004/01/26 Mon PM 10:34:25 EST
> >>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >>Subject: 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).
> >>
> >
> >
>
> --
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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).