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

From: Nelson, Allan <anelson_at_miswaco.com>
Date: Fri, 23 Apr 2004 13:48:11 -0500
Message-ID: <7E0DB3A126BA9146AC30744E2B7E42459741B5@midhouhqxch01.net.smith.com>

You should also separate rollback segments and temp space as they have markedly different use patterns as opposed to data and index segments. Rollback segments tend to be written from beginning to end and over again and temp also tends to be sequential type i/o

Allan

-----Original Message-----
=46rom: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paula_Stankus_at_doh.state.fl.us
Sent: Friday, April 23, 2004 1:40 PM
To: oracle-l_at_freelists.org
Subject: RE: Why "Separating Data and Indexes improves performance" is a myth=3F

So in laying out disks for a new database - perhaps the only thing worth =3D
separate out is the redo based on I/O contention - right=3F =3D20

-----Original Message-----
=46rom: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Stankus, Paula G Sent: Friday, April 23, 2004 2:38 PM
To: oracle-l_at_freelists.org
Subject: RE: Why "Separating Data and Indexes improves performance" is a myth=3F

Daniel,

Clear explanation. =3D3D20

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

-----Original Message-----
=46rom: 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=3F

In a test using a range scan on a non-unique index, I found the =3D3D =66ollowing
sequence. The index is a normal b-tree, so the process may be different

=3D
=3D3D
=66or

bitmaps, IOTs, etc.

Read index. Navigate from the root to branch to leaf blocks. I know =3D =3D3D have a list of rowids.
Read the first block indicated in the first rowid (matching my =3D3D criteria). Add the row of interest to the array. Repeat reading rows until either =3D =3D3D the array is filled (at which point I pass the array to the calling program) =3D =3D3D 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, =3D =3D3D
but by
different sessions. Of course, you could also have 42 sessions each =3D3D 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 =3D
=3D3D
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=3F
> >
> >If they are both accesed at the same time, it will improve =3D3D
performance or
> >not,
> >I don't understand exactly what is this myth about=3F
> >
> >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/
=46AQ 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/
=46AQ 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/
=46AQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


______________________________________________________________________________
This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information.  Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer.  This email may have been monitored for policy compliance.  [021216]

----------------------------------------------------------------
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:46:41 CDT