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