This discussion is archived
10 Replies Latest reply: Jan 10, 2013 2:44 PM by 864103 RSS

index vs table

864103 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    Where did you see this recommendation?
  • 2. Re: index vs table
    Dave Rabone Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks a lot for all members here.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points