RE: Deletion from large table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 23 Aug 2016 14:38:37 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901503393E6_at_exmbx05.thus.corp>

Best access path does vary with circumstances.

If you're expecting lots of inserts while doing the deletes you may find that as the delete progresses the rate slows down and the volume of undo applied for read-consistency climbs. If you see that as a problem it may be that finding an index that lets you walk the big table in reverse order of data arrival may (slightly counter-intuitively) improve performance.

Under any circumstances deleting by tablescan and deleting by index range scan behave differently with respect to index maintenance (this note on big updates also applies to big deletes: http://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/<https://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/> ).

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of John Dunn [JDunn_at_sefas.com] Sent: 23 August 2016 14:39
To: Chris Taylor
Cc: oracle-l_at_freelists.org
Subject: RE: Deletion from large table

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

Received on Tue Aug 23 2016 - 16:38:37 CEST