Re: separate tablespaces for tables and indexes

Here is my take on it.

As others have said, the arguments that were made that the manual splitting of table and index segments reliably improved performance were incorrect. The idea that arose out of this, that one should have separate tablespaces for index and 'data' as part of database tuning was, and remains a myth.

There are arguments around management of objects, but I'm not entirely sure that I buy them in these days of locally managed tablespaces. I can see that indexes *might* require different extent sizes to the base tables, but they might not. Even if they do this is a particular application of the general idea that it is sensible to have a variety of standard extent sizes in a database and objects should go in the (a) tablespace with an appropriate extent sizing policy. The other idea that I have heard is that indexes have different backup requirements to data since they are essentially redundant data. I don't buy this at all, try running the database you care most about with no indexes. I'd be willing to accept this argument a little more if it were accompanied by timings that showed restoration was slower than recreation of the index structures.

When I looked into this suggestion, the earliest reference I could find (there may be earlier ones) was in the DBA Handbook from the v7/v8 days. The genesis was quite interesting - the book talks, correctly about identifying segments that undergo high io rates and attempting to minimize contention by putting them on different disks. It also talks about measuring the actual IO rates that the datafiles were suffering. It seems that it was the example that got promoted to myth territory. I find this interesting because it suggests to me that readers are much more interested in obtaining a list of actions to perform than in understanding where the list came from. Of course in those days the typical disk system was rather different and the available (or at least the information that was generally known to be available) about segment level and block level contention was rather limited.

What I never really understood, maybe its because I know developers who forget indexes but not tables, was that there is another rather large class of objects that tend to get accessed 'together' - that is as part of the same frequently executed execution plan. That is of course related tables (in particular master/detail type tables) and I have never once seen suggestions that ORDERS and ORDER_LINES or even good old DEPT and EMP should be separated. It seems rather obvious to me that one should care about the objects that get accessed together rather than the type of object that they are - but that might be just me.  

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l