Re: Oracle Myths- Tablespace placement answered by Oracle

In article <3ce51c7a$1_16_at_news.teranews.com>, you said (and I quote):
> > call me a dreamer, but if you put tables and indexes in same
> tablespace
> > and spread that tablespace over a number of devices you get EXACTLY
> the
> > same result as above.
>
> You need to look down at the disk heads.

Last thing in my list of priorities, Pablo! :-D

> If we're issuing zillions of
> single I/O's, that's a considerably different profile than zillions of
> I/O's that have some multi_block influence.
> --

It doesn't matter. At all. Think about it. An I/O is an I/O is an I/O. A disk doesn't care less if the block just read is a table block or an index block. Totally irrelevant. It's a block, period. The only thing that may be relevant, remotely, is if it is contiguous to the previous read or not.

Unless you have a VERY specific and VERY narrow scope application, there is simply no need to separate table I/O from index I/O at the database level through tablespaces. It doesn't make any sense unless you are prepared to do all your I/O control through tablespaces and assign one table per tablespace. This was done in the past with DB2 and other databases. No need for that nowadays.

With a modern system, what you do is bunch it up in a single logical unit and spread it over as many devices as you can, using striping or a LVM. Guaranteed to give you as good as or better performance than you doing the work by hand with tablespace separation.

Of course, other considerations enter here. It is safer to separate tables from indexes, because you may want to re-build indexes or reload a table or do other maintenance that that would cause uneven distribution if you had everything in the same tablespace. THAT is the reason for separating.

But most definitely NOT performance. Not with a modern system with an LVM and the ability to stripe devices and controllers.

The point is not that separating tables from indexes into individual tablespaces is no good. The point is that it need not be done if the only issue is performance.

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