本文共 4371 字，大约阅读时间需要 14 分钟。
DELETE FROM (SELECT * FROM EPAD_MENU EM, MSG_DESC MD WHERE MD.MD_TABLE = 'epad_menu' AND MD.MD_FIELD = 'em_id' AND MD.MD_VALUE = EM.EM_ID AND EM.EM_BOOK_CODE = 'aa'）
SQL> create table t1(id int primary key,info varchar2(10));Table created.SQL> create table t2(id int primary key,info varchar2(10));Table created. SQL> insert into t1 values (1,'digoal');1 row created.SQL> insert into t1 values (2,'digoal');1 row created.SQL> insert into t2 values (1,'digoal');1 row created.SQL> insert into t2 values (2,'digoal');1 row created.SQL> commit;Commit complete.下面来写个类似的delete语句 :
SQL> delete from (select * from t1,t2 where t1.id=1 and t2.id=t1.id and t2.info='digoal');1 row deleted.SQL> select * from t2; ID INFO---------- ---------- 1 digoal 2 digoalSQL> select * from t1; ID INFO---------- ---------- 2 digoal
SQL> rollback;Rollback complete.SQL> delete from (select * from t2,t1 where t1.id=1 and t2.id=t1.id and t2.info='digoal');1 row deleted.SQL> select * from t1; ID INFO---------- ---------- 2 digoal 1 digoalSQL> select * from t2; ID INFO---------- ---------- 2 digoalSQL> rollback;
SQL> explain plan for delete from (select * from t2,t1 where t1.id=1 and t2.id=t1.id and t2.info='digoal');Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1934688782----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | DELETE STATEMENT | | 1 | 33 | 1 (0)| 00:00:01 || 1 | DELETE | T2 | | | | || 2 | NESTED LOOPS | | 1 | 33 | 1 (0)| 00:00:01 ||* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 20 | 1 (0)| 00:00:01 ||* 4 | INDEX UNIQUE SCAN | SYS_C0065664 | 1 | | 1 (0)| 00:00:01 ||* 5 | INDEX UNIQUE SCAN | SYS_C0065663 | 1 | 13 | 0 (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T2"."INFO"='digoal') 4 - access("T2"."ID"=1) 5 - access("T1"."ID"=1)19 rows selected.SQL> explain plan for delete from (select * from t1,t2 where t1.id=1 and t2.id=t1.id and t2.info='digoal');Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2064908203----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | DELETE STATEMENT | | 1 | 33 | 2 (0)| 00:00:01 || 1 | DELETE | T1 | | | | || 2 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | SYS_C0065663 | 1 | 13 | 1 (0)| 00:00:01 ||* 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 20 | 1 (0)| 00:00:01 ||* 5 | INDEX UNIQUE SCAN | SYS_C0065664 | 1 | | 0 (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("T1"."ID"=1) 4 - filter("T2"."INFO"='digoal') 5 - access("T2"."ID"=1)19 rows selected. 转载地址：http://fnbva.baihongyu.com/