Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tablespaces and indexes
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...Received on Thu Sep 20 2001 - 10:29:59 CDT
> 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