Re: Deletion from large table

I have generally had good performance with syntax like this:

delete from big_table where id in (select big_table.id from small_table, big_table where small_table.id=big_table.id (+) and small_table.id is null)

On Tue, Aug 23, 2016 at 9:38 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

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

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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