This discussion is archived
13 Replies Latest reply: Nov 16, 2007 7:43 AM by 475167 RSS

Separate TABLESPACE for Indexs [SOLVED]

54033 Newbie
Currently Being Moderated
We have all tables in USERS Tablespace.
But all Indexes in separate TableSpace INDXTBLSPC

DBA's Please
Is it it good Idea keep indexes in separate tablespace ?
  • 1. Re: Separate TABLESPACE for Indexs
    ajallen Pro
    Currently Being Moderated
    It is not necessary, however it does no harm, either.

    It may make management easier, but that is about all it will do for you.
  • 2. Re: Separate TABLESPACE for Indexs
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.
  • 3. Re: Separate TABLESPACE for Indexs
    54033 Newbie
    Currently Being Moderated
    So Billy do you suggest for performance reason it should be seperate ?
  • 4. Re: Separate TABLESPACE for Indexs
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.
  • 5. Re: Separate TABLESPACE for Indexs
    Sven W. Guru
    Currently Being Moderated
    We have all tables in USERS Tablespace.
    But all Indexes in separate TableSpace INDXTBLSPC

    DBA's Please
    Is it it good Idea keep indexes in separate
    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).

    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:
    Sven Weller
  • 6. Re: Separate TABLESPACE for Indexs
    54033 Newbie
    Currently Being Moderated
    INDEXES in another tablespace as the TABLES (don't use USERS).
    Is there as Reason for Not using the Table Space USERS ?
  • 7. Re: Separate TABLESPACE for Indexs
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Hi,
    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.

    Nicolas.
  • 8. Re: Separate TABLESPACE for Indexs
    54033 Newbie
    Currently Being Moderated
    Thanks Nicolas.

    I am marking this thread as SOLVED
  • 9. Re: Separate TABLESPACE for Indexs
    Sven W. Guru
    Currently Being Moderated
    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, ...
  • 10. Re: Separate TABLESPACE for Indexs
    105967 Explorer
    Currently Being Moderated
    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.
    That 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.
  • 11. Re: Separate TABLESPACE for Indexs
    Sven W. Guru
    Currently Being Moderated
    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.
    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.

    But as it has been said already, now you can forget about those hardware problems.
  • 12. Re: Separate TABLESPACE for Indexs
    3096 Newbie
    Currently Being Moderated
    Amusingly, and a little distressingly, the 10g documentation says to separate tables and indexes for performance purposes.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#i1006372

    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.
  • 13. Re: Separate TABLESPACE for Indexs
    475167 Newbie
    Currently Being Moderated
    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.

    Dim