Re: INDEX and DATA Tablespaces.....

From: Tux <aldeburgh_at_optusnet.com.au>
Date: Sat, 20 Sep 2003 18:47:39 +1000
Message-ID: <3f6c14ee$0$22463$afc38c87@news.optusnet.com.au>

Goran wrote:

>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:3f67b208$0$28122$afc38c87_at_news.optusnet.com.au...

>> Alex Ivascu wrote:
>>
>> >
>> > Hari Om wrote:
>> >> I am using Oracle 9.2.0.1 on IBM AIX 5.1L system.
>> >>
>> >> When designing DB and its physical layout.....do I need to SEPERATE
>> >> INDEX TABLESPACE from its DATA TABLESPACE.....Oracle DBA Book by Loney
>> >> says to better seperate these 2 Tablespaces....but some of the folks
>> >> here in the google say it should be on SAME DISK.....kindly
>> >> confirm......Any related links are appreciated.
>> >>
>> >> THANKS!
>> >
>> >
>> > Separate them.  That's the first thing that any I/O Performance tuning
>> > course teaches you.  This should help you, too:
>> >

> http://download-west.oracle.com/docs/html/A97297_01/appg_ofa.htm#sthref787

>> >
>>
>>
>> And one of the first things any *good* I/O Performance tuning course

> should

>> teach you is that that particular piece of advice is total nonsense.
>>
>> HJR

>
>
> With all due respect, how can two (whatever) datafiles on the same disk
> give *the same* (or even better) performance as the same two on separate
> disks?
>
> Regards....

Easy: I read from datafile 1 on disk A, you *then* read from datafile 2 on disk A. Your read didn't conflict with mine. Substitute 'write' with 'read' wherever you choose. The keyword is "then".

Now put it the other way around. You read from *table* 1 *whilst* I read from table 2, both on disk A. Two *tables*, but our I/O conflicts, because the keyword here is "whilst".

Never mind keep tables separate from indexes: try and keep "whilsts" separate from other "whilsts". And that's the point.

Now, try and persuade me that an index access *intrinsically* conflicts with a table access. It doesn't: they're serialised by Oracle.

You deal with I/O conflicts by finding out what it is that is conflicting. If that happens to be DEPT and EMP, so be it. If it happens to be EMP and PK_EMP, so be it too. But you can't say that it is inevitably an index that conflicts with its table... so mindless separation of tables and indexes just because one is a table and one is an index is, er, mindless. They are both segments: they need to be treated as such, which means separate them if they need it, and don't bother if they don't.

The worst thing about this 'separate indexes from tables' myth is that it obscures the real issue: which is that tables might need separating from tables. Or indexes from indexes.

Stupid rules of thumb are, er, stupid.

Regards
HJR Received on Sat Sep 20 2003 - 03:47:39 CDT