10 Replies Latest reply: Jan 10, 2013 4:44 PM by 864103 RSS

    index vs table

    864103
      Hi all ,

      oracle 11g.2 ASM with RAC under RHEL 5

      i know oracle recommended to create table in x tablespace and create index on this table in y tablespace but why ???
      what's the benifts of that ???


      thanks
        • 1. Re: index vs table
          JohnWatson
          Where did you see this recommendation?
          • 2. Re: index vs table
            Dave Rabone
            Once upon a time in the 1980s there may have been a performance advantage in splitting tables and indexes into different table spaces. Even then there were arguments about whether this was a real advantage.

            Assuming that you are running an Oracle version > 6, there is no performance reason for splitting them.

            There may be other reasons, but they are all to do with system management. For example, if you know that tablespace X contains only indexes, then you know that if you ever need to recover tablespace X from backup, then you also have the option of just recreating the indexes instead. That might (or might not) be a better option.
            • 3. Re: index vs table
              EdStevens
              861100 wrote:
              Hi all ,

              oracle 11g.2 ASM with RAC under RHEL 5

              i know oracle recommended to create table in x tablespace and create index on this table in y tablespace but why ???
              OH?!?! You "know" that oracle recommends? Please cite your source to prove that oracle "recommends" this old wives tale.

              what's the benifts of that ???


              thanks
              • 4. Re: index vs table
                user9944600
                As Dave indicated, if you are on ASM, i do not think there is a need to table and indexes on different tablespaces, but you can do so for manageability purposes.
                • 5. Re: index vs table
                  Billy~Verreynne
                  861100 wrote:

                  i know oracle recommended to create table in x tablespace and create index on this table in y tablespace but why ???
                  what's the benifts of that ???
                  Was never an Oracle recommendation as far as I recall. (a lot was however written about it by "experts" and DBAs)

                  There are issues such as transportable tablespaces, wanting different block sizes for index blocks versus data blocks, complex data management and so on, that raise the issue of whether one should consider using different tablespaces for indexes and data.

                  But unless there are actually such issues, the easiest is to use a single tablespace. It makes space management significantly easier. It makes DBA administration easier. And it should have no I/O performance impact as I/O (ito RAID, stripe sets, etc) are dealt with at ASM level - and not at logical storage unit level (such as at tablespace level).

                  My personal preference (as DBA and developer) is to have a single dedicated tablespace per logical database - so the Marketing application and schema will have a single dedicated tablespace, the HR application and schema its dedicated tablespace, etc.
                  • 6. Re: index vs table
                    EdStevens
                    user9944600 wrote:
                    As Dave indicated, if you are on ASM, i do not think there is a need to table and indexes on different tablespaces, but you can do so for manageability purposes.
                    ASM has nothing to do with it. And Dave said nothing about ASM.
                    • 7. Re: index vs table
                      Mich Talebzadeh
                      In the days when you had single packs and multi-packs of few GB, databases used to deploy these tricks to speed up queries. Nowadays with the advent of bigfiles etc no vendor recommeds these breakups.

                      These days when most serious systems use ASM, SAN, Veritas etc, these segration of tables and indexes do not have any performance benefits. Some still deploy them because that is the way they used to do it!.

                      You must forgive me but it is all a bit database Jurassic park.

                      HTH,

                      Mich Talebzadeh
                      • 8. Re: index vs table
                        jgarry
                        Billy  Verreynne  wrote:
                        861100 wrote:

                        i know oracle recommended to create table in x tablespace and create index on this table in y tablespace but why ???
                        what's the benifts of that ???
                        Was never an Oracle recommendation as far as I recall. (a lot was however written about it by "experts" and DBAs)
                        http://docs.oracle.com/cd/E12104_01/books/AnyInstAdm/AnyInstAdmPreInstall4.html
                        http://docs.oracle.com/cd/E22982_01/doc.462/e18818/local.htm#BJEHEFGD
                        http://docs.oracle.com/cd/F49540_01/DOC/server.815/a67772/tables.htm#340
                        http://docs.oracle.com/cd/B10500_01/server.920/a96521/tables.htm#340

                        Google is your recall friend :D

                        (ISTR finding and posting something in 10g, but maybe my recall isn't perfect either. In the end, the myth of separating out indices for performance reasons lasted a very long time, even in the docs. My vague recollection is it was in the DW docs.)
                        • 9. Re: index vs table
                          Billy~Verreynne
                          Not expected to see this in the 8i and 9i Admin guides... interesting. Thanks for the links.

                          Fortunately 11g left behind such dated approaches and concepts. :-)
                          • 10. Re: index vs table
                            864103
                            thanks a lot for all members here.