This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jul 19, 2012 9:20 AM by jgarry RSS

Design question: Indexes and Data - separate tablespaces or not?

garywicke Newbie
Currently Being Moderated
Environment:

Oracle 11.2.0.3 on Solaris 10.5

I realize this is a religious discussion in many circles but I thought I'd throw it out there anyway.

What would be the pros and cons of having the indexes and the data in the same tablespace?

We have a SAN and the mount points published to the server are RAID configured over multiple physical disks.

I'm most concerned with performance rather than manageability.

We potentially could have different block sizes for the different tablespaces if that affects performance.

We're looking at both B-tree and BITMAP indexes.

Thanks very much for your comments, advice and recommendations!!

-gary
  • 1. Re: Design question: Indexes and Data - separate tablespaces or not?
    rp0428 Guru
    Currently Being Moderated
    >
    The install of the application (COTS, not done by me) didn't create any indexes in the tablespace I was re
    >
    Is there something more you wanted to add? This sentence got cutoff in the middle of a word.

    In my opinion the 'separate tablespace' myth has pretty well been busted. There don't appear to be any performance or management/maintenance reasons to use separate tablespaces. The table and each index will have their own segments even if the same tablespace.

    This series of articles by noted author Richard Foote examines, and busts, all of the supposed 'benefits'
    http://richardfoote.wordpress.com/2008/04/16/separate-indexes-from-tables-some-thoughts-part-i-everything-in-its-right-place/

    Here is the premise and opening statement he starts with:
    >
    Although by no means as common as it once was, there’s still some who believe separating indexes in a different tablespace from their parent tables somehow improves performance.

    The theory goes that by having indexes in their own tablespace, we can reduce overall contention issues and thereby improve the overall performance of the database.
  • 2. Re: Design question: Indexes and Data - separate tablespaces or not?
    Paulie Pro
    Currently Being Moderated
    garywicke wrote:
    Environment:

    Oracle 11.2.0.3 on Solaris 10.5

    I realize this is a religious discussion in many circles but I thought I'd throw it out there anyway.

    The database supports a Datawarehoue-ish type application with the largest
    reference table less than 100 million rows. The install of the application (COTS,
    not done by me) didn't create any indexes in the tablespace I was re
    Back when T. Rex roamed the earth, it was a consideration - not any more.

    See: http://richardfoote.wordpress.com/2008/05/02/indexes-in-their-own-tablespace-recoverability-advantages-get-back/

    Basically, with modern storage configurations, there's no point to it.

    HTH,


    Paul...
  • 3. Re: Design question: Indexes and Data - separate tablespaces or not?
    garywicke Newbie
    Currently Being Moderated
    Wow! You guys are too fast for me!!! The article posted before I was done typing and when I finally was finished you guys had already answered!!

    Thanks!

    I have seen the article by Richard Foote but haven't had time to read it yet. It's tops on my list for sure now.

    I'm going to try and mark both your responses as 'Correct' but I'm not sure it will let me. If not, I'll have to go in chronological order with a 'Correct' and a 'Helpful'.

    Thanks again for sharing your expertise!

    -gary
  • 4. Re: Design question: Indexes and Data - separate tablespaces or not?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    Design question: Indexes and Data - separate tablespaces or not?
    1. It is not a design question.
    2. Separating tables and indexes into different TS does not make sense unless the files placed on different HDD spindles.
    If tables and indexes separated into its tablespaces but placed on same spindle and data files are defragmented (blocks are not intermixed), then even worse, it will cause increase of HDD cylinder seek latency.
  • 5. Re: Design question: Indexes and Data - separate tablespaces or not?
    EdStevens Guru
    Currently Being Moderated
    Paulie wrote:
    garywicke wrote:
    Environment:

    Oracle 11.2.0.3 on Solaris 10.5

    I realize this is a religious discussion in many circles but I thought I'd throw it out there anyway.

    The database supports a Datawarehoue-ish type application with the largest
    reference table less than 100 million rows. The install of the application (COTS,
    not done by me) didn't create any indexes in the tablespace I was re
    Back when T. Rex roamed the earth, it was a consideration - not any more.
    I'm not so sure it was, even then. As Richard Foote and others have pointed out, accessing a table via index is inherently a serial process. The IO to get the row address from the index must be completed before that row address can be used to begin accessing the table.

    Perhaps, just maybe, on a single-user, single-disk system. But as soon as you introduce a second process, you introduce the probability that while process A is accessing an index, process B has already queued up his own request. And that request will send the read/write mechanisim off on another search before it gets back to Process A's request for the row from the data table. And that holds true regardless of how primitive or sophisticatd the storage system is.


    >
    See: http://richardfoote.wordpress.com/2008/05/02/indexes-in-their-own-tablespace-recoverability-advantages-get-back/

    Basically, with modern storage configurations, there's no point to it.

    HTH,


    Paul...
  • 6. Re: Design question: Indexes and Data - separate tablespaces or not?
    rp0428 Guru
    Currently Being Moderated
    >
    1. It is not a design question.
    >
    Nonsense - of course it is a design question!

    Part of the work of designing the data model and designing the technical implementation of that data model is determining what tablespaces to use
    and what to use them for.

    1. Locally Managed or dictionary managed?
    2. Bigfile or regular?
    3. Encrypted or not?
    4. AUTOALLOCATE or UNIFORM?
    5. What extent size(s) to use?
    6. What size? MaxSize? Autoextend?
    7. What datafiles to create? Where to create them?
    8. ASM or ASSM?
    >
    2. Separating tables and indexes into different TS does not make sense unless the files placed on different HDD spindles.
    If tables and indexes separated into its tablespaces but placed on same spindle and data files are defragmented (blocks are not intermixed), then even worse, it will cause increase of HDD cylinder seek latency.
    >
    Using the older style of manually creating tablespaces the DBA would specify the actual path for the datafiles. This means they could in some environments control which HDD the files was on. Not so true, or even desirable, anymore.

    But it is most certainly a design question.
  • 7. Re: Design question: Indexes and Data - separate tablespaces or not?
    Paulie Pro
    Currently Being Moderated
    >

    Back when T. Rex roamed the earth, it was a consideration - not any more.
    I'm not so sure it was, even then. As Richard Foote and others have pointed out,
    accessing a table via index is inherently a serial process. The IO to get the row
    address from the index must be completed before that row address can be
    used to begin accessing the table.
    Am I not right in thinking that it was a practice when DBAs would put data on one
    disk and indexes on anohter - this before fancy RAID and SAME strategies?


    Paul...
  • 8. Re: Design question: Indexes and Data - separate tablespaces or not?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    this before fancy RAID
    RAID has nothing to do with it. You can place table's TS to one RAID volume (comprised of several disks) and index TS to another RAID volume (comprised of another several disks).
  • 9. Re: Design question: Indexes and Data - separate tablespaces or not?
    EdStevens Guru
    Currently Being Moderated
    Paulie wrote:
    >

    Back when T. Rex roamed the earth, it was a consideration - not any more.
    I'm not so sure it was, even then. As Richard Foote and others have pointed out,
    accessing a table via index is inherently a serial process. The IO to get the row
    address from the index must be completed before that row address can be
    used to begin accessing the table.
    Am I not right in thinking that it was a practice when DBAs would put data on one
    disk and indexes on anohter - this before fancy RAID and SAME strategies?


    Paul...
    Oh, you are absolutely right in thinking it was a practice. And a common one at that.
    That doesn't mean there was any sound technical reasoning for it. Just like all myths, someone thought it made sense, so started doing it and teaching others to follow suit. Those being taught never questioned it. Eventually some of them even wrote books, so then it was definately seen as "A Good Thing(tm)". And perputuated for many years.
  • 10. Re: Design question: Indexes and Data - separate tablespaces or not?
    Paulie Pro
    Currently Being Moderated
    >


    Hi Ed,

    And perputuated for many years.
    Pesky darn myths <spits tobacco towards spitoon on corner of room>, like weeds. Bit like the
    great BHCR myth of the last darn millenium...


    Paul...
  • 11. Re: Design question: Indexes and Data - separate tablespaces or not?
    EdStevens Guru
    Currently Being Moderated
    Paulie wrote:
    >


    Hi Ed,

    And perputuated for many years.
    Pesky darn myths <spits tobacco towards spitoon on corner of room>, like weeds. Bit like the
    great BHCR myth of the last darn millenium...


    Paul...
    In the interest of full disclosure, I'll have to admit that I followed the practice myself for a few years. At my new job (less than 6 months) we have quite a few 'index' tablespaces. Interestingly, there has been some carelessness about the rigor with which they are used. But that is a battle not worth fighting. While there is no real gain from having dedicated index TS, there is no real harm and I have bigger fish to fry than trying to collapse them.
  • 12. Re: Design question: Indexes and Data - separate tablespaces or not?
    jgarry Guru
    Currently Being Moderated
    I seem to recall part of the persistence of the myth came from the fact that it appeared to work - separating on to different spindles did spread the load. It was the causality that was wrong, and performance could have been even better with actual analysis of disk requests to spread the load as evenly as possible. There were "database health check" scripts that would sort activity by tablespace, a more reasonable thing to do, except few people would compare index separation vs. not, since they already accepted the myth.
  • 13. Re: Design question: Indexes and Data - separate tablespaces or not?
    jgarry Guru
    Currently Being Moderated
    Which RAID, and is there a difference for redo or archive?
  • 14. Re: Design question: Indexes and Data - separate tablespaces or not?
    rp0428 Guru
    Currently Being Moderated
    >
    I seem to recall part of the persistence of the myth came from the fact that it appeared to work - separating on to different spindles did spread the load.
    >
    Then you must not have ever had to use Oracle version 3 or 4 on an IBM PC that had two humongous 10 MB (yes - that is 'M' not 'G') hard drives.
    >
    It was the causality that was wrong
    >
    Au contraire, mein Herr! The causality was absolutely correct. Placing the tables on one physical disk and the indexes on another physical disk did spread the load.
    >
    and performance could have been even better with actual analysis of disk requests to spread the load as evenly as possible.
    >
    Uh - memory fails me as to any tool that was publicly available in late 1970's, early 1980's that could do that.

    The more things change the more they remain the same. Take a large file on today's hard drive and time how long it takes to
    1. copy the file to the same drive
    2. copy the file to a different drive

    Two spindles is still faster. It's just that in a real server environment you typically don't have any control of the spindle being used.

    So from personal experience I can say that this 'myth' began as 'fact' and 'morphed' into 'myth' over time. (Try to say that 3 times fast!)
1 2 Previous Next

Legend

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