As the question states, what are the benefits of doing this given the following information/constraints:

  1. I will not be using partitioning
  2. I will not be placing the tablespaces and its datafiles into different hard disk drives
  3. My schema has lots (maybe around 50 to 100) of small tables and there are only about 5 to 10 tables that will grow in terms of number of records
  4. Expected growth of these 5 to 10 tables can reach up to 100k rows but will not probably reach a million rows

I am planning to segregate these big tables into their own tablespaces instead of mixing it with the tablespace used for the rest of the tables and indexes. I am also planning to place all indexes on a separate tablespace, so the list of tablespace that my user will be using should look something like this:

  • Tablespace_BIG_Table_1...10
  • Tablespace_for_indexes
  • Tablespace_for_the_rest_of_the_tables

Aside from being "neat and organized" with my tablespaces, are there any performance benefits that I can get from this given that I am not using partitioning and only using a single disk, in addition I am only working with a relatively small set of records?

Or should I just stick with placing everything on a single tablespace?


2 Answers 2


Minimal performance benefit, the only benefit in this regard is from reduced fragmentation. But Oracle and most filesystems handle this much better these days than in the past; Oracle handles this better with "automatic segment space management" and the Oracle "filesystem" ASM, though you don't need to use the latter.

It's more a manageability choice these days; personally I wouldn't bother with separate tablespaces, but I do often create one per application. This allows you to use a transportable tablespace export if you so desire.

  • Makes sense, I guess with cases like mine, it's more of a manageability choice, as you said it yourself
    – Avias
    Dec 17, 2013 at 8:12

Indexes "grow" differently than tables, also when you rebuild them (for various reasons) your tablespace can get easily fragmented. It's always good idea to keep these separately.

Also when usign uniform extent allocation you have no other option that to have at least to tablespaces for small tables (reference, enumerations, LOVs) and targe tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.