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

Yes, and my reply was about regular index full scans, according to your question.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> 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" <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).
>

-- 
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).