Re: how can I delete rows without using subquery?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 27 Mar 2000 16:17:07 -0500
Message-ID: <uijvdsk0uid5oivuheepg4jh7r0ervqumn@4ax.com>

A copy of this was sent to "Maggie" <maggiezhou_at_hotmail.com> (if that email address didn't require changing) On Mon, 27 Mar 2000 10:54:31 -0800, you wrote:

>In oracle, can we delete rows after table join? I would like remove the
>records in MR11 table that order_nr and item_nr are same in the TEST but
>item_delivery is sth other than the null and 71. How can I do that?
>
>I try to create the query as follow:
>
>delete from MR11 m, test t
> where (m.order_nr = t.order_nr)
> and (m.item = t.ITEM_NR)
> and (r11.item_delivery is not null and r11.item_delivery != '71' );
>
>However, it show me the error:
>delete from ordq.MR11_ORDERS_TAB m, sumtest r11
> *
>ERROR at line 1:
>ORA-00933: SQL command not properly ended.
>
>I don't want use where exists (subquery) because it take very long time to
>delete thousands of rows. Do you have any idea how can I do that?
>
>

2 different approaches. If M has a primary key or unique constraint on (order_nr, item), the first one is best. Else the second will work and will use an index on M(order_nr,item) to speed up the delete (but T will be sorted first).

Consider:

ops$tkyte_at_8i> create table m ( order_nr number, item number, item_delivery varchar2(5) );
Table created.

ops$tkyte_at_8i> create table t ( order_nr number, item_nr number ); Table created.

ops$tkyte_at_8i> alter table m add constraint m_pk primary key(order_nr, item ); Table altered.

ops$tkyte_at_8i> insert into m values ( 1, 1, '55' );
ops$tkyte_at_8i> insert into m values ( 2, 2, '55' );
ops$tkyte_at_8i> insert into t values ( 1, 1 );
ops$tkyte_at_8i> commit;

Commit complete.

ops$tkyte_at_8i> set autotrace on explain
ops$tkyte_at_8i> delete from
  2 ( select m.*

  3            from m, t
  4           where m.order_nr = t.order_nr
  5             and m.item = t.item_nr
  6             and m.item_delivery is not null
  7             and m.item_delivery != '71' )

  8 /

1 row deleted.

Execution Plan

---

   0 DELETE STATEMENT Optimizer=CHOOSE    1 0 DELETE OF 'T'

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'M'
   5    4         INDEX (UNIQUE SCAN) OF 'M_PK' (UNIQUE)

So, that full scans T and then index accesses M for each row in T to find the rows to delete..... That only works if M is "key preserved".

Now, trying a different way:

ops$tkyte_at_8i> rollback;
Rollback complete.

ops$tkyte_at_8i> delete from m

  2    where (order_nr,item) in ( select order_nr, item_nr from t )
  3      and m.item_delivery is not null
  4      and m.item_delivery != '71'

  5 /

1 row deleted.

Execution Plan

---

   0 DELETE STATEMENT Optimizer=CHOOSE    1 0 DELETE OF 'M'

   2    1     NESTED LOOPS
   3    2       VIEW
   4    3         SORT (UNIQUE)
   5    4           TABLE ACCESS (FULL) OF 'T'
   6    2       TABLE ACCESS (BY INDEX ROWID) OF 'M'
   7    6         INDEX (UNIQUE SCAN) OF 'M_PK' (UNIQUE)

ops$tkyte_at_8i> set autotrace off

So that one full scanned T and sorted it unique and then did the index access into M. It'll be a little slower if T is large, about the same otherwise.

Of course, there are other possible plans depending on your optimzer mode, indexes and so on....

--
http://osi.oracle.com/~tkyte/  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 27 2000 - 15:17:07 CST