RE: Why "Separating Data and Indexes improves performance" is a myth?
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Apr 2004 13:16:24 -0400
I don't split indexes from data for performance reasons because most =
times it doesn't. But splitting indexes from data can help if your =
using non RAID disk storage, especially when a drive fails. And since =
indexes are hit more often that data that drive is going to be the first =
to fail, but not be terminal to the application. Also I split them for =
the purpose of managing the space utilization separately. Especially =
with indexes on tables that have a high insert/delete cycle. You'll =
notice that those indexes grow faster over time, especially when then =
really need a rebuild. Bottom line, don't separate anything just =
because it appears to be "good practice". Do it for a reason that you =
can defend.
Now will someone please pass the shotgun. I've a duhveloper to shoot =
because having his indexes in a separate tablespace from the data =
"improves performance"!! Like who needs an index on a 100 row x 4 =
column table in the first place?
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Jared.Still@xxxxxxxxxxx [mailto:Jared.Still@xxxxxxxxxxx]
Sent: Friday, April 23, 2004 12:47 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: 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?
From the perspective of a single query, the indexes are not accessed at=20
the same time=20
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=20
doesn't really
matter. You have several queries access both data and indexes. There =
can=20
be=20
contention for either one.
What matters is the performance of your system under load. If you have =
10=20
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=20
physically separating
the indexes and data will not improve performance.
It would be very likely though that it would decrease performance,=20
particularly on=20
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@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:
