Re: INDEX and DATA Tablespaces.....

"Hari Om" <hari_om_at_hotmail.com> wrote in message news:d1d5ebe4.0309161412.281f760_at_posting.google.com...

> I am using Oracle 9.2.0.1 on IBM AIX 5.1L system.

A good combination.

> When designing DB and its physical layout.....do I need to SEPERATE
> INDEX TABLESPACE from its DATA TABLESPACE.....

Only if you:

1- Can predict the separation will help in reducing a hot spot. 2- Want to facilitate future administration and management.

> Oracle DBA Book by Loney
> says to better seperate these 2 Tablespaces

Loney's book might be slightly out of date on this or even might actualy be recommending that separation be functional rather than performance related. In which case it is fine.

> ....but some of the folks
> here in the google say it should be on SAME DISK.....

No. They say that it makes NO difference to performance. Which is a totally different proposition.

> kindly
> confirm......Any related links are appreciated.
>

If you want to preempt maintenance of indexes possibly causing contention to tables, or to retain the ability of distributing portions of your tables to other tablespaces (presumably on other disks) and so on, then yes: separate the tablespaces. Do also separate your tables over more than one tablespace. By function. Or type. Or size.

If all you are concerned only with performance, then apply "SAME" and don't worry about this I/T separation: its benefits to performance might have been true 10 years ago with more basic I/O systems. With today's hardware, there isn't such a diff.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam