Saturday, August 01, 2009

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

No comments: