Unfortunately it’s a nightly thing….whilst updates are still going on….
John
From: Chris Taylor [mailto:christopherdtaylor1994@xxxxxxxxx]
Sent: 23 August 2016 14:38
To: John Dunn
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx<mailto:JDunn@xxxxxxxxx>> 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
