Today I discussed with a database user who wanted to have different tablespaces for different partitions.
I had to disagree with his points, even though sometimes I think the split of tablespaces on DWH system can be useful for future migrations (then could be done tablespace per tablespace).
However is again the question of data+indexes on same tablespace or not. Since long years I see no sense on the separation. There is no performance gain, only more administrative work. Once a colleague told me about the fact that in the need of quicker recover from backup, you could just recover the data tablespace and not the indexes. We did not have dataguard in use at the time. Nowadays if you need a quick recover, then you use dataguard.
– What if you want to recover one table from a backup of 1 month ago?
– Ok, there might be still extreme situations where more smaller tablespaces make sense.
But on my life as a DBA, I want that database users (my customers) do not care about the keyword “tablespace” into their DML/DDL. My strategy is to give a default tablespace per application.
Found also a nice discussion from Charles Hooper blog about the tablespace split or not:
http://hoopercharles.wordpress.com/2012/02/17/tables-and-their-indexes-should-be-located-on-different-disks-what-is-wrong-with-this-quote/