Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Not exactly duplicate of your question but most of the benefits of the tablespaces: dba.stackexchange.com/a/49796/25478 –  Mindaugas Riauba Dec 12 '13 at 10:15
    
@MindaugasRiauba point taken –  Avias Dec 13 '13 at 6:43

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Makes sense, I guess with cases like mine, it's more of a manageability choice, as you said it yourself –  Avias Dec 17 '13 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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