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:38:07 -0400
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@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?
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@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/
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:
