RE: Why "Separating Data and Indexes improves performance" is a myth?
- From: <Paula_Stankus@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Apr 2004 14:55:16 -0400
duh - of course. =20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Nelson, Allan
Sent: Friday, April 23, 2004 2:48 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Paula_Stankus@xxxxxxxxxxxxxxx
Sent: Friday, April 23, 2004 1:40 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Stankus, Paula G
Sent: Friday, April 23, 2004 2:38 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Daniel Fink
Sent: Friday, April 23, 2004 1:15 PM
To: oracle-l@xxxxxxxxxxxxx
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.
1. Read index. Navigate from the root to branch to leaf blocks. I know =
=3D3D
=3D3D3D
have a
list of rowids.
2. 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).
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, =
=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@xxxxxxxxxxxxx
> >put 'unsubscribe' in the subject line.
> >--
> >Archives are at //www.freelists.org/archives/oracle-l/
> >FAQ is at //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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
=3D46AQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
=3D46AQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
=3D46AQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
