Re: When does Oracle use 'Index Fast Scan'

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" <ORACLE-L_at_fatcity.com> 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
>
>
> ryan.gaffuri_at_cox.net 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 <davehau123_at_netscape.net>
> >>Date: 2004/01/27 Tue AM 11:54:26 EST
> >>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >>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
> >>
> >>
> >>ryan.gaffuri_at_cox.net 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 <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: 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
  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).