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

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Fri, 23 Apr 2004 11:15:11 -0600
Message-id: <40894F1F.228ECF31@sun.com>

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.

So, the data and index is not physically accessed at the same time.

However, at the time session 1 is performing step 2, session 2 may be performing step 1, so the data and index may be read at the same time, but by different sessions. Of course, you could also have 42 sessions each reading the same data block at the same time.

Daniel Fink

Wolfgang Breitling wrote:

> The myth is that "they are both accessed at the same time". They are not.
> At least not by the same session processing a single sql statement.
>
> At 10:13 AM 4/23/2004, you wrote:
> >Hi
> >Does any one kwnows please,
> >Why "Separating Data and Indexes improves performance" is a myth?
> >
> >If they are both accesed at the same time, it will improve performance or
> >not,
> >I don't understand exactly what is this myth about?
> >
> >http://www.niall.litchfield.dial.pipex.com/OracleMyths.zip
> >
> >Thanks
> >
> >
> >Juan Carlos Reyes Pacheco
> >OCP
> >Database 9.2 Standard Edition
> >
> >
> >----------------------------------------------------------------
> >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
> >-----------------------------------------------------------------
>
> regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------

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 - 12:29:12 CDT