Re: separate tablespaces for tables and indexes
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Dec 2004 08:59:05 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
