remove 18 million records from the table.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 37
- Joined: Sun Aug 01, 2004 1:18 am
remove 18 million records from the table.
Hi,
Is ther any way i can delete 18 million records with without using trancate. I can't use trancate as my DBA was like that. The delete statement will definetly squaze our DB. Is there any other way to accomplish this task.. I can drop the table, but there are many referential integrities over there . Suggest me with fastest method to clear the table.
Is ther any way i can delete 18 million records with without using trancate. I can't use trancate as my DBA was like that. The delete statement will definetly squaze our DB. Is there any other way to accomplish this task.. I can drop the table, but there are many referential integrities over there . Suggest me with fastest method to clear the table.
Thanks,
Cnu
We work together to workout...
Cnu
We work together to workout...
Truncate. Or drop and recreate. Or ask your friendly neighborhood DBA to do it for you.
Only thing is, if there are 'many referential integrities', you may not be able to do either... unless they are disabled first. How were you planning on resolving those issues once the table is emptied - will you be loading the records back?
Only thing is, if there are 'many referential integrities', you may not be able to do either... unless they are disabled first. How were you planning on resolving those issues once the table is emptied - will you be loading the records back?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 37
- Joined: Sun Aug 01, 2004 1:18 am
Hi Chullet
There is arround 20 million records and i need to delete all records older than 2005 march.chulett wrote:Truncate. Or drop and recreate. Or ask your friendly neighborhood DBA to do it for you.
once the table is emptied - will you be loading the records back?
Thanks,
Cnu
We work together to workout...
Cnu
We work together to workout...
DataStageCnu,
"truncate" and "drop table with recreate" are high-speed methods of getting rid of the entire table contents as quickly as possible. This is very different from what you want to do! Each database has different methods of speedily removing rows, and the method used also depends on the existence of indices, triggers and constraints on the table as well as how many records are left. If your table contains 21 million records and you want to delete 20 million of them it might be faster to copy the 1 million, truncate the table, and move the 1 million back.
This question is best answered by your DBA.
"truncate" and "drop table with recreate" are high-speed methods of getting rid of the entire table contents as quickly as possible. This is very different from what you want to do! Each database has different methods of speedily removing rows, and the method used also depends on the existence of indices, triggers and constraints on the table as well as how many records are left. If your table contains 21 million records and you want to delete 20 million of them it might be faster to copy the 1 million, truncate the table, and move the 1 million back.
This question is best answered by your DBA.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
You've confused the issue by mentioning 'truncate' when in fact you didn't want to remove all of the records in the table.
What I would do to remove 18 of 20 million records would be to create a new table and copy the 2 million records you want to keep into it. Then all that's left is a quick pair or renames to swap the tables around. Or a drop and a rename.
Of course, you've still got those pesky RI issues to deal with...
What I would do to remove 18 of 20 million records would be to create a new table and copy the 2 million records you want to keep into it. Then all that's left is a quick pair or renames to swap the tables around. Or a drop and a rename.
Of course, you've still got those pesky RI issues to deal with...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 37
- Joined: Sun Aug 01, 2004 1:18 am
Hi ArndW, srinivasulu and Chulett,
Ur ideas are really appriciated but the situation here is different . If i drop the table, i will be loosing all references and constraints on it. I believe, srinivasalu suggestion looks better here, but to my feeling, this is not the all time solution. What to do if it is not the table, instead a "View" . We should find some other way to delete records as fast as "Truncate" without truncate privilege. It is always a problem with our beloved DBA's .
Ur ideas are really appriciated but the situation here is different . If i drop the table, i will be loosing all references and constraints on it. I believe, srinivasalu suggestion looks better here, but to my feeling, this is not the all time solution. What to do if it is not the table, instead a "View" . We should find some other way to delete records as fast as "Truncate" without truncate privilege. It is always a problem with our beloved DBA's .
Thanks,
Cnu
We work together to workout...
Cnu
We work together to workout...
And just to further beat this poor horse, your situation isn't all that different from anyone needing to delete a large quantity of records from a table. Don't make it more difficult than it is by thinking you are doing something special.
Yes, dropping a table will cause things like contraints and FK references to be dropped as well. So? Simple scripts can be put together (heck, TOAD can generate them for you if you are on Oracle) that do all the disabling, dropping, recreating and re-enabling that you would need. If you need help with that, or don't have the permissions you'd need, leverage your friendly neighborhood DBA-man for that task.
What to do when it is a "View"? Switch to the base table or tables that make up the view. Nothing magical there.
And there is no other way to delete records as fast a truncate, primarily because it doesn't really delete them. Besides, you need to stop worrying about truncating - you said you need to delete most of the records from a table, not all of them. Truncate is not the weapon of choice for you then.
I still will put forth the notion that instead of deleteing 90% of the records in a table, it is vastly more effecient to save the 10% you want to keep in a new table and then swap the two. Not only is it quicker and probably measured in minutes to accomplish with good scriptage, you get a nice clean organized table out of it - not the limp skin of an elephant that been reduced in size internally to a dingo.
However, if you want to take the hours you'll need to transactionally delete 18 million records then all I can say is... have fun!
Yes, dropping a table will cause things like contraints and FK references to be dropped as well. So? Simple scripts can be put together (heck, TOAD can generate them for you if you are on Oracle) that do all the disabling, dropping, recreating and re-enabling that you would need. If you need help with that, or don't have the permissions you'd need, leverage your friendly neighborhood DBA-man for that task.
What to do when it is a "View"? Switch to the base table or tables that make up the view. Nothing magical there.
And there is no other way to delete records as fast a truncate, primarily because it doesn't really delete them. Besides, you need to stop worrying about truncating - you said you need to delete most of the records from a table, not all of them. Truncate is not the weapon of choice for you then.
I still will put forth the notion that instead of deleteing 90% of the records in a table, it is vastly more effecient to save the 10% you want to keep in a new table and then swap the two. Not only is it quicker and probably measured in minutes to accomplish with good scriptage, you get a nice clean organized table out of it - not the limp skin of an elephant that been reduced in size internally to a dingo.
However, if you want to take the hours you'll need to transactionally delete 18 million records then all I can say is... have fun!
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am