Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Candy.

Asked: May 06, 2013 - 4:59 pm UTC

Last updated: May 07, 2013 - 2:46 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

There is still discussion on both sides about putting indexes in a separate tablespace than the data. For 11G Release 2 on a RAC system using ASM what is your opinion about putting the tablespace for indexes in the same tablespace as the data?

and Tom said...

Tablespaces are not a performance tool.

Tablespaces are a book-keeping, administrative, make your life better and more enjoyable tool.

Tablespaces can always be used to reduce your backup/recovery needs.



Now, as to why they are not a performance too (and never have been...). The goal is even IO over all of your devices for your datafiles. We use file system striping for that. So, using ASM or whatever file system you have, we achieve even IO by striping. NOT by placing a table on this device, an index on that device. The days of being able to do that went by in the 1980's. So, don't think about it from a performance angle.

However, do think about it from a book keeping, does it make my life better perspective. Does it help me organize the data in my database if I keep the tables in tablespace T1 and their indexes in I1? That is mostly a personal choice.

From a backup and recovery perspective, it could make sense to keep indexes separate from other types of data. If you lost an index datafile - it might be much faster/easier to just drop and recreate the index from the table data for some people. Also, given that in some databases - indexes represent 50,60,70% or more of the data space - there could be something said for not having to back them up.

So, regardless of RAC, forgetting about ASM - the reason for separating index from table data isn't performance related, it is however a "does it make you feel good" or "can I reduce my backups significantly and do I want to rebuild an index if I lose a datafile instead of restoring it" conversation to have.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

martin, May 07, 2013 - 12:16 am UTC

Tom, would you think having index in the same TS as data segment and frequently dropping/rebuilding the index could cause more Tablespace fragmentation ?(causing IO spread over the database/disk which otherwise could have been more contiguous)

Tom Kyte
May 07, 2013 - 2:46 pm UTC

why would you frequently drop and rebuild an index??

but, no, using a locally managed auto-allocate tablespace would allow for space to be efficiently and effectively used and reused and reused.

and striping would make it so that it really just doesn't matter about the IO - we would WANT it to be spread out - striping by design spreads it out, on purpose.

tablespaces

Candy Peck, May 07, 2013 - 1:34 pm UTC

Thank you very much for taking the time to answer my question.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.