The FF index scan reads all the block in the index, using multiblock
reads. The kernel then discards the branch blocks. If sorting of the
result set is required, then this is a separate row source operation,
because the rows don't come out of the segment in sorted order.
To see why, imagine the following index "picture", which I'll draw
sideways, using indentation to denote the parent-child relationship.
It's a pretty stupid-looking index (Oracle can have many more children
per branch block than I'll draw), but it'll work to illustrate. I'll
label each block in the index with file#:block#.
23:14
23:15
23:18
23:19
23:22
23:20
23:17
23:25
23:24
23:21
23:16
23:23
To range-scan the rows in the index, the kernel would read the following
blocks from file 23 using 'db file sequential read', in this order:
18, 19, 22, 17, 25, 21, 16, 23.
To FF scan the index, Oracle would read the following blocks using 'db
file scattered read' (assume a value of 8 for my example), in this
order:
14-21, 22-25
In using the results, the kernel would discard blocks 14, 15, 20, and
24. As I said, if the kernel needed the rows in the remaining blocks to
be in logical order (as opposed to the physical order in which they were
obtained), then the kernel would have to sort them.
This is how I think FF scan works. To be sure, trace a simple FF scan,
using extended SQL trace (event 10046 level 8).
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- SQL Optimization 101: 12/8-12 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
Mladen Gogala
Sent: Thursday, October 23, 2003 10:34 PM
To: Multiple recipients of list ORACLE-L
function?
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.
> [EMAIL PROTECTED]
> 214.954.1781
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>
> > 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.
> > > [EMAIL PROTECTED]
> > > 214.954.1781
> > >
> > > > -----Original Message-----
> > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Behalf Of
> > > > [EMAIL PROTECTED]
> > > > 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: <[EMAIL PROTECTED]>
> > > > > Date: 2003/10/23 Thu PM 03:09:26 EDT
> > > > > To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
> > > > > 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: <[EMAIL PROTECTED]
> > > > > INET: [EMAIL PROTECTED]
> > > > >
> > > > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED]
> > > > INET: [EMAIL PROTECTED]
> > > >
> > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Cary Millsap
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
