Re: Tablespaces and indexes

On Thu, 20 Sep 2001 08:29:59 -0700, "Pete Sharman" <peter.sharman_at_oracle.com> wrote:

>There is NO performance benefit from isolating data and index.

Hehehe! Now now, Pete. Don't you go destroying the "market" of all those experts out there making a motza out of "splitting hairs"! :-)

>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.

Precisely.
Allow me to expand on this.

Splitting databases into interminable strings of tablespaces (with the record being the old DB2 recommendation of one table per tablespace!!!) is of very relative value in this day and age for purposes of I/O optimization. At least with ORACLE.

As you pointed out, there may be other considerations: similarity of space allocation being one that springs to mind (for dictionary-allocation tablespaces). Or partitioning.

Fact is: most people nowadays use some form of SAN or other RAID-based architecture or a very good LVM. With these, there is no point whatsoever is spending large amounts of time splitting your database into lots of tablespaces, be they indexes or table or mixed.

One is much better off efficiency-wise to group table/indexes/whatever by *type of usage*, then allocating them in a tablespace dedicated to that type. The classic way a few years back was the "small/medium/large" technique for tablespaces. Still works, but I reckon there are better ways. Then we control the IO throughput by allocating appropriate "devices" to it.

As in: want more speed for writing? Allocate striped disks to that tablespace. Want more speed for reading? Use mirroring. And so on. The possibilities with something like an EMC or a good volume manager are tremendous. Beats splitting tablespaces anyday.

I've always been one to solve IO by looking at the hardware, not the spread of the database across tablespaces. Much easier to achieve results. This has caused a few "clashes" over the years, but I've got the gains to prove it, so I don't care. Glad to see someone else stick the neck out on this one too. Well done.

>--
>HTH. Additions and corrections welcome.

well, you got my $0.02 anyways...

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Fri Sep 21 2001 - 04:44:28 CDT