Oracle SQL : delete from (query), delete which table's row?

今天群里面一位朋友问了一个问题如下 :

                                 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的数据？

于是找了个测试库测试一下 :

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.