Home » SQL & PL/SQL » SQL & PL/SQL » Deletes with respect to table sizes (11g)
Deletes with respect to table sizes [message #660728] Sat, 25 February 2017 23:19 Go to next message
manikandan23
Messages: 34
Registered: February 2017
Member
In a huge table containing a couple of TBs, and 1% of data has to be deleted, what's the best method to do it?

Also, in the same huge table containing a couple of TBs, and 10% of data has to be deleted, what's the best method to do it?
Re: Deletes with respect to table sizes [message #660737 is a reply to message #660728] Sun, 26 February 2017 01:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I do not know what "best" means for you. Is there a problems with a simple DELETE? The simplest solution is often a good candidate for the "best" solution.
Re: Deletes with respect to table sizes [message #660738 is a reply to message #660728] Sun, 26 February 2017 01:17 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi

does the table has any FK relationship from other tables?
what is the cascading option is it restrict, delete or set to null

If the table is too large and independent table with billions of row this is what we do


create new table using APPEND hint by excluding 10% or 1% of the desired data
drop the existing table
rename the new table to the existing table


garan
Re: Deletes with respect to table sizes [message #660762 is a reply to message #660738] Sun, 26 February 2017 17:05 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
Thank you guys and any other suggestions?

The table is independent table.
Re: Deletes with respect to table sizes [message #660778 is a reply to message #660762] Mon, 27 February 2017 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
No - there's the two options:
1) delete
2) create table as select / drop / rename

I'd go with option 1.
Re: Deletes with respect to table sizes [message #660780 is a reply to message #660762] Mon, 27 February 2017 03:24 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
manikandan23 wrote on Sun, 26 February 2017 23:05
Thank you guys and any other suggestions?

The table is independent table.
You could avoid the delete completely and instead create a VPD policy that would stop the rows being visible. A kind of virtual delete.
However, until you define "best" I don't see how you can evaluate the options.
Re: Deletes with respect to table sizes [message #660822 is a reply to message #660780] Mon, 27 February 2017 09:11 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
What version of the database do you have. And what is your criteria for deleting. Your table sounds like it would be a good candidate for being partitioned. Dropping a partition is very quick and in 12c it even handles global indexes by ignoring the rows pointing to the dropped partition until the database gets around to cleaning up the global index.
Previous Topic: Return all rows if no matching condition found
Next Topic: can we use column alias in having clause
Goto Forum:
  


Current Time: Thu Mar 28 08:00:30 CDT 2024