13 Replies Latest reply: Nov 16, 2007 9:43 AM by Dr.Dimitri RSS

    Separate TABLESPACE for Indexs [SOLVED]

    54033
      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
          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
            Billy~Verreynne
            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
              So Billy do you suggest for performance reason it should be seperate ?
              • 4. Re: Separate TABLESPACE for Indexs
                Billy~Verreynne
                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.
                  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
                    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
                      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
                        Thanks Nicolas.

                        I am marking this thread as SOLVED
                        • 9. Re: Separate TABLESPACE for Indexs
                          Sven W.
                          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
                            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.
                              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
                                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
                                  Dr.Dimitri
                                  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