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

From: <Jared.Still_at_radisys.com>
Date: Fri, 23 Apr 2004 09:46:37 -0700
Message-ID: <OFF49FDB55.1881B113-ON88256E7F.005BA59F-88256E7F.005C2920@radisys.com>

> 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?
>From the perspective of a single query, the indexes are not accessed at
the same time
as the data. Take a look at a trace file to verify it if you like.

>From the perspective of many queries executing simultaneously, it still
doesn't really
matter. You have several queries access both data and indexes. There can be
contention for either one.

What matters is the performance of your system under load. If you have 10 disks in
a RAID 0 with all indexes and data residing on it and the performance is somewhat
lackluster, splitting those disks into two 5 disk RAID 0 drives and physically separating
the indexes and data will not improve performance.

It would be very likely though that it would decrease performance, particularly on
full table scans and fast full index scans.

Jared

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 - 11:43:47 CDT