Re: Why "Separating Data and Indexes improves performance" is a myth?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 23 Apr 2004 19:54:17 +0100
Message-ID: <005501c42964$60feb470$7102a8c0@Primary>

Any when Oracle gets the index pre-fetch working properly, it will be even more extreme. The action is:

fetch all relevant rowids from the index leaf blocks sort by table block ids
visit the table blocks in order - with no need to revisit the index leafs.

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 July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Daniel Fink" <Daniel.Fink_at_Sun.COM> To: <oracle-l_at_freelists.org> Sent: Friday, April 23, 2004 6:15 PM Subject: Re: Why "Separating Data and Indexes improves performance" is a myth?

In a test using a range scan on a non-unique index, I found the following sequence. The index is a normal b-tree, so the process may be different for bitmaps, IOTs, etc.

Read index. Navigate from the root to branch to leaf blocks. I know have a list of rowids.
Read the first block indicated in the first rowid (matching my criteria). Add the row of interest to the array. Repeat reading rows until either the array is filled (at which point I pass the array to the calling program) or another data block is indicated (read the next data block).
Repeat 2 until all the rowids matching the criteria are read.
Read the next leaf block. Repeat 2 & 3 until all is done.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri Apr 23 2004 - 13:52:11 CDT