RE: Deletion from large table
Date: Tue, 23 Aug 2016 15:45:41 +0000
Message-ID: <SN2PR0701MB7657C225F16205722632C3CD4EB0_at_SN2PR0701MB765.namprd07.prod.outlook.com>
<snip>
One more…most Oracle database features that are cool and helpful cost additional $$$. ☺
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Tuesday, August 23, 2016 9:17 AM
John,
Beaten to the punch by my brilliant spouse...
Augmenting her insightful suggestion, if you can partition it in such a way that the rows to be deleted are isolated into separate partitions away from the rows being inserted/updated? Queries aren't a concern, but non-isolated DML would be a problem.
An example would be a DATE column, assuming that rows being deleted are older than a certain time, while rows being updated are less than a certain time.
If so, then you can employ a parallel CREATE TABLE ... AS SELECT or INSERT /*+ APPEND */ SELECT to extract rows rows you wish to keep from the partition which you are currently performing deletions. This will result in another standalone table which you can now exchange with the original partition. An INSERT operation is always far faster than any UPDATE or DELETE operation, especially when performed in bulk using APPEND (i.e. direct-path load).
Other benefits are several...
I've presentations, white-papers<http://evdbt.com/papers/>, SQL and PL/SQL scripts<http://evdbt.com/scripts/>, and videos<http://evdbt.com/videos/> on these operations on my website, if this helps?
Above all, please remember these two adages...
Hope this helps...
-Tim
Above all, please remember these two adages...
To: dbakevlar_at_gmail.com; JDunn_at_sefas.com
Cc: Chris Taylor <christopherdtaylor1994_at_gmail.com>; oracle-l_at_freelists.org
Subject: Re: Deletion from large table
On 8/23/16 08:53, Kellyn Pot'Vin-Gorman wrote:
Have you considered partitioning this table and can it “possibly” be partitioned in a way that would isolate these rows?
Just a wild thought….
Kellyn
On Aug 23, 2016, at 7:39 AM, John Dunn <JDunn_at_sefas.com<mailto:JDunn_at_sefas.com>> wrote:
Unfortunately it’s a nightly thing….whilst updates are still going on….
John
From: Chris Taylor [mailto:christopherdtaylor1994_at_gmail.com]
Sent: 23 August 2016 14:38
To: John Dunn
Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: Re: Deletion from large table
Is this a one time thing, or a regularly occurring thing? (A one time data cleanup versus a nightly routine)
If it's a one time data cleanup (or rarely needed), I'd recommend saving off the rows you want to keep into another table, truncate the big_table and reload the rows from the temporary table you created to save the rows you wanted.
Delete is one of the (if not THE) single most expensive operation you can run in a database (but I'm sure you're aware of that but wanted to mention it).
Chris
On Tue, Aug 23, 2016 at 5:17 AM, John Dunn <JDunn_at_sefas.com<mailto:JDunn_at_sefas.com>> wrote: I need to delete large numbers of rows from a large table based upon whether a record exists in a small table.
I am currently using :
delete from big_table where not exists (select 1 from small_table s where s.id<http://s.id/> = b.id<http://b.id/>)"
big_table may have up to 100,000 rows for the same id value. small_table will only have one row per id value
Is there a better way to code this?
John
Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 23 2016 - 17:45:41 CEST