remove 18 million records from the table.

DataStageCnu · Post by **DataStageCnu** » Wed Dec 28, 2005 7:07 pm

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.

chulett · Post by **chulett** » Wed Dec 28, 2005 7:28 pm

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?

DataStageCnu · Post by **DataStageCnu** » Wed Dec 28, 2005 11:48 pm

Hi Chullet

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?

There is arround 20 million records and i need to delete all records older than 2005 march.

ArndW · Post by **ArndW** » Thu Dec 29, 2005 2:26 am

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.

Sreenivasulu · Post by **Sreenivasulu** » Thu Dec 29, 2005 4:48 am

HI ,

A logical solution. Hope this helps:

You can delete records older than march 2005 in batches.
For eg records older than march 2000,march 2001,march 2002,march 2003,march 2004,march 2005...so on.

This would definitely be faster than a single delete.

Regards
Sreeni

chulett · Post by **chulett** » Thu Dec 29, 2005 8:29 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...

DataStageCnu · Post by **DataStageCnu** » Fri Dec 30, 2005 11:52 pm

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

.

kumar_s · Post by **kumar_s** » Sat Dec 31, 2005 12:10 am

HI,
So you mean to say that you dont know what is the constraints available for that table?
Why cant you drop all constraints and recreate them??

-Kumar

chulett · Post by **chulett** » Sat Dec 31, 2005 8:43 am

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!

Sreenivasulu · Post by **Sreenivasulu** » Mon Jan 02, 2006 1:20 am

Hi,

Chullet's observation is an apt one. Instead of deleting 90 percent of
records transactionally you can create a temp table with 10 percent records and then swap as suggested by him.

Before doing this have the ddl/dmls for FK,indexes generated from TOAD or any other pl/sql tool

Regards
Sreeni