Re: Index - Range Scan question

Thanks Jonathan.

What comes to mind next is that IF Oracle does not do table-prefetching (for whatever reason) and the range scan spans thousands of index leaf blocks, would Oracle parallelize reading index leaf blocks from disk and fetching corresponding data rows.

I'd like to elaborate on the above with a simple hypothetical example:

Say each index leaf block contains only one row id and Oracle is not table pre-fetching. The query requires a range scan of 10-20, so there are 10 index leaf blocks to be read from disk. Oracle reads the first index leaf block, gets the row id from it and goes to the heap to fetch the corresponding row. While it is fetching the corresponding row from the heap, Oracle reads the next index leaf block from disk in parrallel.

Is the above concept present in Oracle or is the software written to be sequential in its processing, meaning that it always follows the rule, read index first then the data.

Is the above concept viable?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c43ie3$m0r$1_at_hercules.btinternet.com>...
> It depends on the version of Oracle.
> In 8.1, the only option is to read a rowid
> from a leaf block, go to the row, return to
> the leaf block get the next rowid, and so
> on - stepping to the next leaf block only
> when all the relevant entries from the current
> leaf block have been used.
>
> In Oracle 9i, based on costing, and the overall
> structure of the query, Oracle can read all the
> relevant rowids from the range of leaf blocks,
> sort them into datablock address order, and then
> visit the table to get all the rows. This is known
> as table-prefetching, and I've only seen it happen
> once in one very special case. It is visible, though
> in the execution plan of a simple nested loop join
> where the inner table is visited with a range scan.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> April 2004 Iceland http://www.index.is/oracleday.php
> June 2004 UK - Optimising Oracle Seminar
>
>
> "Toby Brown" <toby_brown_at_optusnet.com.au> wrote in message
> news:2a84f50c.0403270135.144c7f65_at_posting.google.com...
> > How does an index range scan happen? I want to know how Oracle works
> > under the hood when it does an index range scan. Say the range scan is
> > from 10-20. Does Oracle get to the first index leaf node (10), get the
> > rowid and then go to the table, grab that row and then come back to
> > the index and read the next index leaf node (11) and repeat the
> > process? OR Does Oracle read all the row IDs from all relevant leaf
> > nodes and only then go to the table?
> >
> > many thanks
Received on Sat Mar 27 2004 - 09:10:03 CST