Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tablespaces and indexes

Re: Tablespaces and indexes

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: Thu, 20 Sep 2001 08:29:59 -0700
Message-ID: <84oq7.30$vj2.7045@inet16.us.oracle.com>


I will don my cape as 'Slayer of Oracle Myths' and climb on to my soapbox. There is NO performance benefit from isolating data and index. The operation of an indexed access will serially and synchronously perform any IOs required to traverse the index to get a row id and then, synchronously, perform any IO required to read a data block. Note each of these operations are done serially, there is no overlap between the index IO and the data IO. For this reason there is no performance benefit.

The purpose of IO tuning is to get consistent response times from all of the disk operations. If the number of operations on data and indexes are comparable then splitting the data and indexes is one method of doing that. However I have seem many cases where customers have had IO problems precisely because they have believed this myth. A data warehouse where most of the operations are scans rarely access the index disks. A high volume OLTP system where many queries are satisfied in the index only and there is very good locality of access on the data would rarely access the data disks. There are reasons for splitting data and indexes, mainly for management purposes, but performance is not one of them.

--
HTH.  Additions and corrections welcome.

Pete
Author of "Oracle8i: Architecture and Administration Exam Cram"
Now got a life back again that the book is released!

"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook

"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA

"Dale Edgar" <dale_at_databee.com> wrote in message
news:3ba9dcbe.12372123_at_news.btclick.com...

> Hi Michiel
>
> On Thu, 20 Sep 2001 13:32:35 +0200, "Michiel Brunt" <mbrunt_at_inergy.nl>
> wrote:
> >However, I have heard others say it is better to store indexes in a
seperate
> >index tablespace.
>
> The basic idea in tablespace layouts is to reduce the "contention" (ie
> waiting) of the disks as the heads move back and forth. Many operating
> systems can also do simultaneous reads from from different disks.
>
> If your tablespaces are NOT located on disks that are striped then it
> is better to have indexes in their own tablespace and on a different
> physical disk. If the disks are striped together into a logical volume
> it is less important to separate the data and indexes since you really
> don't know which physical disk is being hit anyways. In that case, if
> one is trying to get the ultimate in disk efficiency one would put the
> index tablespace on a different stripe set and make sure each stripe
> set uses a different group of disks.
>
> Regards
> Dale
> ----------
> DataBee - Oracle DataBase Subsets
> The simple and easy way to create small versions of large Oracle
databases. http://www.databee.com
Received on Thu Sep 20 2001 - 10:29:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US