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:55:16 -0400
Message-ID: <80D4A99A2715674EB2D256DAD89219F60222AE7A@dohsmail02.doh.ad.state.fl.us>

duh - of course. =20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Nelson, Allan Sent: Friday, April 23, 2004 2:48 PM
To: oracle-l_at_freelists.org
Subject: RE: Why "Separating Data and Indexes improves performance" is a myth?

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-----
=3D46rom: 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=3D3F

So in laying out disks for a new database - perhaps the only thing worth
=3D3D

separate out is the redo based on I/O contention - right=3D3F =3D3D20

-----Original Message-----
=3D46rom: 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=3D3F

Daniel,

Clear explanation. =3D3D3D20

And if 42 sessions are likely reading the same block at the same time =
=3D3D
=3D3D3D

then wouldn't it be better to have more disks for the RAID stripe - then
=3D3D
=3D3D3D

to sacrifice those disks for the indexes=3D3F

-----Original Message-----
=3D46rom: 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=3D3F

In a test using a range scan on a non-unique index, I found the =3D3D3D
=3D66ollowing

sequence. The index is a normal b-tree, so the process may be different
=3D3D
=3D3D3D
=3D66or

bitmaps, IOTs, etc.

Read index. Navigate from the root to branch to leaf blocks. I know =
=3D3D
=3D3D3D
have a list of rowids.
Read the first block indicated in the first rowid (matching my =
=3D3D3D
criteria). Add the row of interest to the array. Repeat reading rows until either =
=3D3D
=3D3D3D
the array is filled (at which point I pass the array to the calling program)
=3D3D
=3D3D3D
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, =
=3D3D
=3D3D3D

but by
different sessions. Of course, you could also have 42 sessions each =
=3D3D3D

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 =
=3D3D
=3D3D3D

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=3D3F
> >
> >If they are both accesed at the same time, it will improve =3D3D3D

performance or

> >not,
> >I don't understand exactly what is this myth about=3D3F
> >
> >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/


=3D46AQ 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/


=3D46AQ 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/


=3D46AQ 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
-----------------------------------------------------------------
----------------------------------------------------------------
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:53:08 CDT