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 <[EMAIL PROTECTED]> > Date: 2004/01/26 Mon PM 10:34:25 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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. > > > > > > > > [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
