What are the performance benefits of segregating database objects into different tablespaces?

Question

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?

Answer 1

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.

Answer 2

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.