DELETE from a view

Sometime, we need to do "scrub" against a big table, for example,delete (or update) some rows based on the condition in a second small table. We can probably perform this task through "delete from a view" if the second table has primary key constraint. The following test demonstrated the importance of table order if we do "delete from a view". We need to put the target big table first in the FROM list, otherwise we may end up deleting the small table.

SQL> create table t1
  2  as
  3  select rownum id,
  4      rpad('*', 50) pad
  5  from all_objects
  6  where rownum <=100;

Table created.

SQL>
SQL>
SQL> create table t2
  2  as
  3  select rownum*3 id
  4  from all_objects
  5  where rownum <=10;

Table created.

SQL>
SQL> alter table t1 add constraint t1_pk primary key(id);

Table altered.

SQL> alter table t2 add constraint t2_pk primary key(id);

Table altered.

SQL>
SQL> execute dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats(user,'t2');

PL/SQL procedure successfully completed.

SQL>
SQL> -- Order: t1, t2
SQL> delete from plan_table;

3 rows deleted.

SQL> explain plan for
  2  delete from
  3  ( select t1.id
  4    from t1,
  5         t2
  6    where t2.id = t1.id
  7  );

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 2755785190

-----------------------------------------------------------------------
 Id   Operation            Name   Rows   Bytes  Cost (%CPU) Time  
-----------------------------------------------------------------------
   0  DELETE STATEMENT               10     60      1   (0) 00:00:01
   1   DELETE              T1                                     
   2    NESTED LOOPS                 10     60      1   (0) 00:00:01
   3     INDEX FULL SCAN   T2_PK     10     30      1   (0) 00:00:01
*  4     INDEX UNIQUE SCAN T1_PK      1      3      0   (0) 00:00:01
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T2"."ID"="T1"."ID")

16 rows selected.

SQL>
SQL> -- Order: t2, t1
SQL>
SQL> delete from plan_table;

5 rows deleted.

SQL> explain plan for
  2  delete from
  3  ( select t1.id
  4    from t2,
  5         t1
  6    where t2.id = t1.id
  7  );

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 525412351

-----------------------------------------------------------------------
 Id   Operation            Name   Rows   Bytes  Cost (%CPU) Time  
-----------------------------------------------------------------------
   0  DELETE STATEMENT               10     60      1   (0) 00:00:01
   1   DELETE              T2                                     
   2    NESTED LOOPS                 10     60      1   (0) 00:00:01
   3     INDEX FULL SCAN   T2_PK     10     30      1   (0) 00:00:01
*  4     INDEX UNIQUE SCAN T1_PK      1      3      0   (0) 00:00:01
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T2"."ID"="T1"."ID")

16 rows selected.

SQL> spool off