B*tree indexes are ALWAY ordered. That's the way they're created and searched.
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. 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.
This sounds like a question for asktom or ixora (Tom Kyte or Steve Adams).
Wolfgang Breitling and J. Lewis might also know.
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).
Received on Thu Oct 23 2003 - 22:34:25 CDT
