RE: index full scan over an index fast full scan in an analytic function?

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> Mladen Gogala
> Sent: Thursday, October 23, 2003 10:34 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: index full scan over an index fast full scan in an analytic
> function?
>
>
> B*tree indexes are ALWAY ordered. That's the way they're created
> and searched.

Vladimir provided the details and references that I left out (and made only passing reference to in prior responses in an effort to be brief) ;-) Assumed people were aware of some of the things, such as reading index blocks ordered or not ordered, single block reads versus multi-block reads, referred to in my prior responses. And I'm pretty sure the poster of the original question already understood the basic differences between the two approaches or else wouldn't have asked the question -- e.g. why is it choosing a method that uses single block reads versus a method that uses multi-block reads. From the question, it appeared the person was already aware of the single block reads versus multi-block reads differences, or else wouldn't have asked.

Docs:

"Fast Full Scan: This is an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. Fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation. It reads the entire index using multiblock reads (unlike a full index scan) and can be parallelized."

Note the comments about (1) not eliminating sort operations, and, (2) multi-block reads (unlike an index scan which typically uses single block reads).

> I don't know the difference between full index scan and fast full
> index scan.
> I know that the latter is used when the tble rows are not needed.

Prior can also be used when the table rows are not needed (and even when they are), and, *can* be used in some cases to avoid a sort operation, often times noted by NOSORT, when applicable, in the execution plan. This was the gist of prior responses, that maybe the index full scan, even with the single block reads, was used instead of an index fast full scan and it's multi-block reads, in an effort to avoid a sort. We don't know since we weren't provided the details, and even if that is the reason, we don't know that it was more efficient. Simply relaying when and why an index full scan might be used instead of a fast full scan when both options are available.

> Sounds like
> both methods are reading all leaf blocks, from start to finish, using
> multiblock read. I am not aware of any difference between the two methods.

Big differences between index fast full scans and index full scans, as already noted. Look at execution plans and how there might be differences with regards to sorting. And we all know docs (referenced above) can be wrong sometimes, so if doing a level 8/12 trace or looking at the v$ waits and you happen to catch some waits, you might notice the difference in the p3 value.

> This sounds like a question for asktom or ixora (Tom Kyte or Steve Adams).
> Wolfgang Breitling and J. Lewis might also know.

Regards,

Larry Elkins
elkinsl_at_flash.net
214.954.1781

>
> On 2003.10.23 23:14, Larry Elkins wrote:
> > Because when doing an index range scan things are read ordered? Very
> > different from an index fast full scan where blocks are simply
> grabbed where
> > they might lie?
> >
> > Regards,
> >
> > Larry G. Elkins
> > The Elkins Organization Inc.
> > elkinsl_at_flash.net
> > 214.954.1781
> >
> > > -----Original Message-----
> > > From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> > > Ryan
> > > Sent: Thursday, October 23, 2003 9:34 PM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Re: index full scan over an index fast full scan in
> an analytic
> > > function?
> > >
> > >
> > > why would you not need a sort with a full index scan and need
> one with a
> > > fast full scan?
> > > ----- Original Message -----
> > > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > > Sent: Thursday, October 23, 2003 5:19 PM
> > > function?
> > >
> > >
> > > > Possibly to avoid a sort operation (assuming that you might be
> > > able to get
> > > > away with a NOSORT when doing the full index scan)? It
> might be deciding
> > > > that the benefit of the multi-block reads for the fast full
> > > scan are more
> > > > than offset by the sort operation that would be needed (and
> might not be
> > > > needed when doing the full index scan).
> > > >
> > > > Regards,
> > > >
> > > > Larry G. Elkins
> > > > The Elkins Organization Inc.
> > > > elkinsl_at_flash.net
> > > > 214.954.1781
> > > >
> > > > > -----Original Message-----
> > > > > From: ml-errors_at_fatcity.com
> [mailto:ml-errors_at_fatcity.com]On Behalf Of
> > > > > rgaffuri_at_cox.net
> > > > > Sent: Thursday, October 23, 2003 2:39 PM
> > > > > To: Multiple recipients of list ORACLE-L
> > > > > Subject: Re: index full scan over an index fast full scan in
> > > an analytic
> > > > > function?
> > > > >
> > > > >
> > > > > i cant attach the 10053 trace. it has proprietary info. There
> > > > > isnt much in analytic explain plan either.
> > > > >
> > > > > does anyone know in general why a full scan would be faster than
> > > > > a fast full scan?
> > > > > >
> > > > > > From: <rgaffuri_at_cox.net>
> > > > > > Date: 2003/10/23 Thu PM 03:09:26 EDT
> > > > > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > > > > Subject: index full scan over an index fast full scan in an
> > > > > analytic function?
> > > > > >
> > > > > > I have an index on the two columns used in this query. Why
> > > > > would the optimizer choose an index full scan over an index fast
> > > > > full scan?
> > > > > >
> > > > > > My question isnt why an index is used, but the type of
> index scan?
> > > > > >
> > > > > > select *
> > > > > > from (select col1, col2,
> > > > > > dense_rank()
> > > > > > over (partition by col1
> > > > > > order by col2 desc)tab
> > > > > > from mytable)
> > > > > > where tab = 1
> > > > > >
> > > > > > --
> > > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > > > --
> > > > > > Author: <rgaffuri_at_cox.net
> > > > > > INET: rgaffuri_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: <rgaffuri_at_cox.net
> > > > > INET: rgaffuri_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: Larry Elkins
> > > > INET: elkinsl_at_flash.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: rgaffuri_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: Larry Elkins
> > INET: elkinsl_at_flash.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).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.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: Larry Elkins
  INET: elkinsl_at_flash.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).