Re: separate tablespaces for tables and indexes

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Tue, 14 Dec 2004 12:41:38 +0000
Message-Id: <1103028098l.3064l.2l@medo.noip.com>

Richard, I beg to differ.

On 12/14/2004 04:54:43 AM, Richard Foote wrote:
> In some cases, separating your indexes can actually *increase*
> contention.

>=20

> Why ?

>=20

> Because, generally one accesses many more "table" blocks than "index"
> blocks
> and index blocks have a greater tendency to remain cached or be =20
> cached
> when
> required. Therefore, there are generally many more PIOs associated
> with your
> table tablespaces than their associated index tablespaces if you
> separate
> them. A look at most statspack reports will reveal this.

If you look at the total amount of I/O, then leaving tables and indexes together will cause the number of I/O requests equal to the sum of =20 total I/O requests needed to read/write indexes and requests needed to =20 read/write tables. So, if you leave them together, your tablespace =20 files will be hotter still.There are two main principles used for =20 separating objects in different tablespaces:

Separating by logical grouping (putting related objects together)
Separating by size (tablespaces for small, medium, large, XL and XXL objects). This was recommended by Guy Harrison, back in the time when T-Rex was ruling the earth.

Either of the two types of separation causes tablespace to be hotter if =20 it contains both types of objects, because total amount of I/O will be =20 larger. Also, separating the two increases resilience of the database. =20 If tablespace containing only indexes becomes terminally corrupted, you =20 can simply rebuild indexes elsewhere without data loss. If it happens =20 to a data tablespace, one has to do recovery.

--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 14 2004 - 06:36:53 CST