|
View previous topic :: View next topic
|
| Author |
Message |
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
Hi All,
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.
Thanks in advance for your help and suggestions. |
|
| Back to top |
|
 |
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi suresh1624
| Quote: |
| Any solutions other than partioning are also welcome. I need know how to define the partioning so as to rotate the partiotions |
Do you really don't want any suggestion regarding partition?
Raghu |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
I believe Suresh is looking for suggestions of how to do (manage) the partitioning or if someone might recommend a method without using partitioning.
How many rows are in each month? How many queries use data from multiple months? |
|
| Back to top |
|
|
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
| 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. |
|
| Back to top |
|
|
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
| 4 million row for each month and 15 months of data will be avbl in the table |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
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. |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
There is a REORG option to DISCARD. You can clean the table during your normal reorg window. Sample syntax from Utility guide is,
| Code: |
| REORG TABLESPACE DSN8D81A.DSN8S81E DISCARD FROM TABLE DSN8810.EMP WHEN (WORKDEPT = ’D11’) SHRLEVEL NONE COPYDDN SYSCOPY |
More details in the utility guide.
I think the best option if you don't want PARTITIONING.
Else if you have segmented tablespace, alter to locksize tablespace nad then delete. It should be fast.
Or, partition the table, syntax in SQL Reference. |
|
| Back to top |
|
|
|
|
