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" <tanel.poder.003_at_mail.ee>
> Date: 2004/01/27 Tue AM 11:19:27 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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" <ORACLE-L_at_fatcity.com>
> 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
> >
> >
> >
> > ryan.gaffuri_at_cox.net 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 <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: Tanel Poder
> INET: tanel.poder.003_at_mail.ee
>
> 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 - 10:59:37 CST
