Printable View
Hi All,
Could you guys suggest me what is the better approach to use tablespaces in partition table?
I have very huge data and partitioned by range weekly. Here question is: Shall we use separate tablespace for each partition or we can use same tablespace for multiple partitions?
Please give some points advantages and disadvantages in each case.
it reallyt doesnt matter, just spread your IO out
It's more a case of personal tast, ease of management etc. As Davey says a primary aim of yours should be to provide maximum IO bandwidth for the way your application accesses the data. We can't answer that as we don't know your application, or your disk setup.
I've worked at places where each partition has it's own tablespace and it's fine but you need to know what you're trying to achieve by either method.
Personally I prefer them to be separate, I feel like I have more flexibility, but other people think there's too many tablespaces to manage in a large, highly partitioned db.
One good point is that if you keep all the data for a specific week or set of weeks in separate tablespace, you can in some point in time make the TS read only, when data will not change anymore and hence make the backup easyer and faster ( since you backup the read only tablespaces only once)
Later you could put the TS on read only media, and even plug it out of the database and plug it back in when needed ( that's possible since 9i and there is special feature for that in 10g)
So to summarize, this will allow you to know where physically the data for specific period resides and hence drop/move/archive/backup easyer
Of cource that maight be not relevant at all in your case