my requirement is as follows.
I need to partition the table based on month and yr (which are the columns of the table) as the table contains huge data for each month and it currently holds 15 months of data.
The oldest month data should be dropped by rotating the partition. Currently the data for the new month is added and the old month data is deleted. Since the volume of data is huge, deleting the data with a query escaltes the lock and getting resource unavbl.
Any solutions other than partioning are also welcome. I need know how to define the partioning so as to rotate the partiotions.
There are approximately 4M rows and in future it might increase to 8M. Currently i have suggested to unload the whole data excluding the oldest data and load it back with RESUME NO REPLACE instead of deleting the data with delete. I want to know the pros and cons of going for partitioning the table and rotating it.
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
I believe part of a successful implementation will take into account when and how the data is used.
How many keys/indexes are defined for these rows? What is the data volume for a month of data.
One of my smaller loads loads about 2.4 million rows in just a few minutes.
What i'd try if your requirement was mine is to unload the complete table, copy (using your sort product) the unloaded data deleting the oldest month. Then merge the new month data with the 12 months kept and reload everything into the table.