Re: Deletion from large table

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> 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
> 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

--
http://www.freelists.org/webpage/oracle-l