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: <ryan.gaffuri_at_cox.net
INET: ryan.gaffuri_at_cox.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).
Received on Tue Jan 27 2004 - 06:59:27 CST
