Re: Why "Separating Data and Indexes improves performance" is a myth?
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 23 Apr 2004 11:15:11 -0600
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
-----------------------------------------------------------------
Other related posts:
