Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: When does Oracle use 'Index Fast Scan'

Re: Re: When does Oracle use 'Index Fast Scan'

From: <ryan.gaffuri_at_cox.net>
Date: Tue, 27 Jan 2004 12:04:26 -0800
Message-ID: <F001.005DE365.20040127120426@fatcity.com>


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: <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 - 14:04:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US