| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: index full scan over an index fast full scan in an analytic function?
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.
> 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
> > > > >
> >
-- 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: Cary Millsap INET: cary.millsap_at_hotsos.com 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 Fri Oct 24 2003 - 12:19:25 CDT
 |
 |