i thought an index_fs only read 1 block per i/o? same with an index range scan because they are using random access? ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 27, 2004 5:29 PM
> An index fast full scan and an index full scan both need to access all > the blocks of an index. The only difference between them is that the > index_ffs accesses the blocks in the order of the blocks (and uses > multiblock read), whereas the index_fs accesses the blocks in the order > of the b tree index. In terms of # logical I/Os, they are exactly the same. > > OTOH, an index range scan by definition is a _range_ scan, and need to > access only a subset of the blocks of an index. Because of this, it'll > have a lower # logical I/Os than an index_ffs. > > Regards, > Dave > > > [EMAIL PROTECTED] wrote: > > >ive found that index_ffs typically incur higher logical I/Os that index range scans. so its not just access speeds. > > > > > >>From: David Hau <[EMAIL PROTECTED]> > >>Date: 2004/01/27 Tue AM 11:54:26 EST > >>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >>Subject: Re: When does Oracle use 'Index Fast Scan' > >> > >>This is where the access time of your disks (or SAN) makes a difference. > >> If your disks have really fast access time, then a random-access > >>pattern would not cause much performance degradation and so a range scan > >>would not be slow at all, even though it's traversing the b-tree index > >>structure. If you're only striping together disks with relatively slow > >>access time (e.g. using a striped IDE disk array), then you have high > >>throughput but not that fast an access time. In this case, fast full > >>index scan would be much faster than an index range scan because the > >>fast full scan reads the blocks sequentially and a sequential disk I/O > >>requires only positioning the head once (assuming the disk is not > >>fragmented). The rest of the time depends on the throughput. If you > >>stripe together a large enough number of IDE disks, then your throughput > >>is great but your access time is still the access time of a single IDE > >>drive which is not that fast. > >> > >>This is assuming you need to do a physical I/O to obtain the blocks. Of > >>course, if the blocks already reside in the buffer cache, then it's a > >>different story. > >> > >>Regards, > >>Dave > >> > >> > >>[EMAIL PROTECTED] wrote: > >> > >> > >> > >>>btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. > >>> > >>>So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. > >>> > >>>test it and hint your queries > >>> > >>> > >>> > >>> > >>> > >>>>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: 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: Ryan 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).
