This content has been marked as final. Show 13 replies
No. It is not a good idea. And I fail to see any management issues that make it easier to manage index objects in a separate tablespace. (and this is from personal experience on many databases - case in point, I'm planning to eliminate an index tablespace on a large TB database as it is does nothing but makes space management very painful)
The only valid reason (or one of the very few) is performance. Where a smaller block size for indexes provides performance gains as the block sizes used for data tablespace is too large.
Only if this does show a real and tangible performance improvement. Some physical table designs (think images/spatial/blobs) may work better with a large block size. You may not want to use the same large block size for the index tablespace. Or you may be using different type of LUNs from a SAN with physically different block sizes per LUN.
This is one of the primary reasons why Oracle changed from a single blocksize for all tablespaces to allow one to specify a blocksize per tablespace - allowing you the flexibility to have tablespaces with different blocksizes.
Is this common though? Not in my experience/environment. But yes - there can be performance related considerations that requires one to use different blocksizes for tables and indexes, which then requires separate tablespaces for indexes and data.
We have all tables in USERS Tablespace.The usual advice is to keep INDEXES in another tablespace as the TABLES (don't use USERS). However with modern storage systems there is no reason anymore for doing this, but for some very rare circumstances (different default storage parameter like extent size, etc).
But all Indexes in separate TableSpace INDXTBLSPC
Is it it good Idea keep indexes in separate
The reason why it was good (still could be good) to have them in a separate tablespace is that index and table are accessed almost syncronously. Read a bit from the index, read a bit from the table, read a bit from the index, read a bit from the table, and so on.
The advice was to put the table tablespace and the index tablespace on separate hard drives, so the they could be read in independantly from each other. For modern storage systems (RAID) you don't care about the physical storage and access anymore. The optimization (parallel read, etc.) is all done by the storage system. I think one of the reasons for the "invention" of tablespaces was that you can put them on separate hard drives/disks.
Message was edited by:
I think the problem is in our human brain. Not merge indexes and tables to be more comfortable to understand. Like in a book, it's more difficult to find something if you put and split the summary somewhere inside the text, and not in a separate chapter. But, as already explained earlier, Oracle doesn't need (any more) this human logic.
A real world scenario looks like this:
- an application schema/user "A" where all the tables are
- some database users "U" who have access the application schema "A"
The users have the default tablespace USERS
The application should store the tables in not in users, but in a separate tablespace(s), like "A_COLOSO".
When the other database users create there own tables they usually use the default tablespace. So that they don't come into conflict with the A.
It is easy for DBAs to find out any non-application logic tables by looking at the tablespace and drop them ;)..
But this question would be better asked in a DBA forum. other keywords that influence tablespace usage are:
QUOTAS, transportable tablespaces, ASM, ...
The reason why it was good (still could be good) toThat is exactly the reason why it NEVER MADE SENSE to separate them as they are and were never used at the same time. It is and was always to read first an index block, then a data block, index block, data block. So it always needs on average the normal block seek time, regardless where the block resides.
have them in a separate tablespace is that index and
table are accessed almost syncronously. Read a bit
from the index, read a bit from the table, read a bit
from the index, read a bit from the table, and so
It is and was always to read first an index block, then a data block, index block, data block.Not true. It was usually faster NOT to reposition the hard disk header to a completly other sector and go back, go forth, go back, etc. When you store the table on disk1 and the index on disk2 this has been noticeably faster than when both were on the same hard disk because of reposition times for the r/w-headers. At least this is what I learned quite a while ago.
So it always needs on average the normal block seek time, regardless where the block resides.
But as it has been said already, now you can forget about those hardware problems.
Amusingly, and a little distressingly, the 10g documentation says to separate tables and indexes for performance purposes.
Tom Kyte mentioned that one valid separation strategy may be to place segments that are generally accessed by multiblock or single block reads on different devices, so that read-ahead at the storage layer doesn't kick in on a (single block)index range scan. Seemed like a good idea to me.
That's very much of interest in data warehouses, of course, and may apply to OLTP systems with a batch reporting component outside of normal operational hours. Of course one might be getting close to this separating on tablespaces by object size already, and just need to take it a stage further by using different devices.
The doc says:
Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace.
That's a big differnece to just use different tablespaces. You must make sure, that the datafiles are located on different disks. But often today thats not possible any more as you only get virtaul disks when using a SAN.