| 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: RE: index full scan over an index fast full scan in an analytic function?
:)
I didn't study that stuff very much in my last five years at Oracle. Fast-full index scans weren't around when I was digging in the dirt. Anyway, things change across versions, and it's impossible to remember everything in one brain anyway...
So,... test!
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-----
rgaffuri_at_cox.net
Sent: Friday, October 24, 2003 12:30 PM
To: Multiple recipients of list ORACLE-L
analytic function?
you worked for oracle for 10 years and you have to guess? how secret do
they keep these internals documents on the algorithms?
>
> From: "Cary Millsap" <cary.millsap_at_hotsos.com>
> Date: 2003/10/24 Fri PM 01:19:25 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: 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
> > > > > >
> > >
> ---------------------------------------------------------------------
> > > > > > 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
> >
-- 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: 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 - 18:59:24 CDT
 |
 |