Re: delete statement taking forever

One generic 'rule', which applies to both rule-based and cost-based optimizing:
if you can rewrite your in-subquery as a correlated exists-query you should do that.

delete from activities a
where exists
(select 'x' from mds_removed_id
 where entity_id = contact_entity_id)

Hth,

Sybrand Bakker, Oracle DBA

<dbanguyen_at_hotmail.com> wrote in message news:7onflh$256$1_at_nnrp1.deja.com...
> Hello, I hope someone can help me with a simple delete statement. Here
> is the statement and the qplan. The appropriate fields are indexed.
> Will it help to analyze the table with compute statistics? The
> activities table has about 700k records and the mds_removed_id has
> 60k. Any way to optimize this statement? Also, I am finding that
> there is a lot of disk swaps on the server when running this.
> Thanks for your help,
> Dave
>
> explain plan
> set statement_id = 'del_mds' for
> delete from activities where contact_entity_id in
> (select entity_id from mds_removed_id);
>
> Q_PLAN
> ------------------------------------
> DELETE STATEMENT
> NESTED LOOPS
> VIEW
> SORT UNIQUE
> INDEX FULL SCAN MDS_REMOVED_ID_IDX
> TABLE ACCESS BY ROWID ACTIVITIES
> INDEX RANGE SCAN R_ACTIVITIES7_FK
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>
Received on Mon Aug 09 1999 - 16:54:39 CDT