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

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Fri, 23 Apr 2004 14:38:07 -0400
Message-ID: <80D4A99A2715674EB2D256DAD89219F60222AE77@dohsmail02.doh.ad.state.fl.us>

Daniel,

Clear explanation. =20

And if 42 sessions are likely reading the same block at the same time = then wouldn't it be better to have more disks for the RAID stripe - then = to sacrifice those disks for the indexes?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Daniel Fink Sent: Friday, April 23, 2004 1:15 PM
To: oracle-l_at_freelists.org
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.

1. Read index. Navigate from the root to branch to leaf blocks. I know = have a list of rowids.
2. 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).
3. Repeat 2 until all the rowids matching the criteria are read.
4. 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
-----------------------------------------------------------------
----------------------------------------------------------------
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:35:11 CDT