Home » RDBMS Server » Performance Tuning » Multiple Tablespaces
Multiple Tablespaces [message #135496] Wed, 31 August 2005 17:30 Go to next message
David King
Messages: 8
Registered: December 2004
Junior Member
Hi Gang!

I have a question that I am hoping to get some guidance on. Our system tables that are static in nature, that have an average transaction load (a few thousand a day), and then tables that get loaded via sqlldr with massive amounts of data on a monthly basis.

Right now we have all tables on the same tablespace. Is it a good idea to put these in their own tablespaces? For example, the static tables in static_tablespace, the daily transaction tables in daily_tablespace, and the others in a different tablespace?

My fundamental question is: Is it good practice to spread your tables across tablespaces based on their usage or to have them all in one tablespace?

Thanks in advance for the help!

Re: Multiple Tablespaces [message #135525 is a reply to message #135496] Thu, 01 September 2005 00:18 Go to previous messageGo to next message
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Hey "Divide & Rule".

Splitting is always a good practice for tuning. U jst need to check the load on ur tables and spread them accordingly in diff-diff tablespaces.

But it is maily beneficial when u really hav those tablespaces stored under diff-diff disks.

Read this for Load Balancing & Striping.


Re: Multiple Tablespaces [message #135621 is a reply to message #135496] Thu, 01 September 2005 09:56 Go to previous message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I would disagree with the statement that splitting them is always good for performance.

To me, it is more a matter of your own administrative convenience, and a matter of your own system characteristics. There is no always.

Do keep in mind, as was mentioned, that you'll want to keep an eye on datafile location. But, if you have a large SAN disk system that will spread your data in a raid 1+0 across all disks, then it won't matter one bit because each datafile will be in the same boat.

Only way to know for sure is to test it in development.

Your static, read only, tables should probably consider compression. Also your monthly load tables.
Previous Topic: Union takes more time please help
Next Topic: Using Hint
Goto Forum:

Current Time: Fri Dec 09 14:25:34 CST 2022