Yes, and my reply was about regular index full scans, according to your question.
Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 27, 2004 6:59 PM > my question pertains to regular 'index full scans' NOT index fast full scans. > > any ideas? I rarely ever find this to be an optimal index access method for anything. > > > > From: "Tanel Poder" <[EMAIL PROTECTED]> > > Date: 2004/01/27 Tue AM 11:19:27 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: 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" <[EMAIL PROTECTED]> > > 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 > > > > > > > > > > > > [EMAIL PROTECTED] 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 <[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: 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: Tanel Poder > > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
