Partitioning Database
-
This is a request I recieved and I'm not sure the best way to go about this.
"I would like to start partitoning our production database statring with table WC_ASSET_TXN_F. Basically we'd like to partion 1/1/05 through 9/30/05. We'll keep the last 3 months of 2005 for the first 3 months of 2006. In April 2006 we can then add the last 3 months of 2005 to the partitioned section and keep all of 2005 together."
Any help would be greatly appreciated. -
SQL2000 or SQL2005?
-
sorry 2000
-
I would consider having a set of archive tables that are basically structurally identical to your existing tables.
Your existing tables contain the data up to 3 months old, your new archive tables contain data over 3 months old.
I would also create views that perform a UNION ALL to provide user access to data from the present day back through time.
For example
Your existing table is WC_ASSET_TXN_F
Your archive table would be WC_ASSET_TXN_F_Archive
Your view would be vw_WC_ASSET_TXN_F
As your archive data is static, apart from your monthly migration from your active tables, you can be more creative with the indexes on your table to improve searching on the archived data without affecting insert performance for the rest of your application.
At update time you can keep a simple script that does the following.
Drop extra archive indexes
Append 3 months old data into the archive.
Recreate the extract archive indexes.
Of course if you upgrade to SQL2005 you would simply use the new partitioning facilities but hey we all have to dream!
-
I really appreciate the suggestions. This database is just too huge and growing rapidly. Can you give me any tips on views? Or possible links? I still consider myself a newbie and haven't had to create a view in about a year or so. Again, thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply