Re: Index - Range Scan question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 27 Mar 2004 09:44:35 +0000 (UTC)
Message-ID: <c43ie3$m0r$1@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 - 03:44:35 CST