今天群里面一位朋友问了一个问题如下 :
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'）
为什么删除的是 MSG_DESC的数据？
于是找了个测试库测试一下 :
下面来写个类似的delete语句 :
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 digoal
SQL> select * from t1;
ID INFO
---------- ----------
2 digoal
把t1.数据删了, t2的没有动.
下面把t1和t2的顺序换一下 :
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 digoal
SQL> select * from t2;
ID INFO
---------- ----------
2 digoal
SQL> rollback;
结果把t2的数据删了, t1没动.
来从执行计划看看是啥原因 :
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.