RE: Deletion from large table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 23 Aug 2016 10:41:48 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90150339343_at_exmbx05.thus.corp>

No, other than adding some hints, perhaps, to ensure that the most efficient plan is taken. There are really only two options: hash anti-join or filter subquery; the pattern in the data, the access paths you can take into the large data set, and the size of the small data set can make a difference to which strategy is the better.

For some thoughts on this see: http://jonathanlewis.wordpress.com/2015/04/13/not-exists/

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 11:17
To: oracle-l_at_freelists.org
Subject: Deletion from large table

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

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

Received on Tue Aug 23 2016 - 12:41:48 CEST