delete 的优化

drop table a;
drop table b;
create table a(city varchar2(20),tel_num number,plags number);
insert into a values('济南','13791135771','1');
insert into a values('聊城','13791135771','1');
insert into a values('青岛','13891136721','2');
commit;
create table b(city varchar2(20),tel_num number);
insert into b values('济南','1379113');
insert into b values('聊城','1310012');
insert into b values('青岛','1389113');
commit;
CITY                    TEL_NUM      PLAGS
-------------------- ---------- ----------
济南                 1379113577          1
聊城                 1379113577          1
青岛                 1389113672          2
SQL> select * from b;
CITY                    TEL_NUM
-------------------- ----------
济南                    1379113
聊城                    1310012
青岛                    1389113
delete from a
 where rowid in (select a.rowid
                   from a
                  inner join b
                     on (b.tel_num = substr(a.tel_num, 1, 7))
                    and a.city <> b.city);


commit;

SQL> select * from a;
CITY                    TEL_NUM      PLAGS
-------------------- ---------- ----------
济南                 1379113577          1
青岛                 1389113672          2

也可以进一步优化（删除少量数据时）：

delete /*+ qb_name(outer) leading(b@inner) use_nl(a@outer) */

from a
 where rowid in (select /*+ qb_name(inner) */
                  a.rowid
                   from a
                  inner join b
                     on (b.tel_num = substr(a.tel_num, 1, 7))
                    and a.city <> b.city);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 73255138
--------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | DELETE STATEMENT             |          |     1 |    24 |     9  (23)| 0
|   1 |  DELETE                      | A        |       |       |            |
|   2 |   NESTED LOOPS               |          |     1 |    24 |     9  (23)| 0
|   3 |    VIEW                      | VW_NSO_1 |     2 |    24 |     7  (15)| 0
|   4 |     SORT UNIQUE              |          |     1 |   124 |            |
|*  5 |      HASH JOIN               |          |     2 |   124 |     7  (15)| 0
|   6 |       TABLE ACCESS FULL      | A        |     3 |   111 |     3   (0)| 0
|   7 |       TABLE ACCESS FULL      | B        |     3 |    75 |     3   (0)| 0
|   8 |    TABLE ACCESS BY USER ROWID| A        |     1 |    12 |     1   (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("B"."TEL_NUM"=TO_NUMBER(SUBSTR(TO_CHAR("A"."TEL_NUM"),1,7)))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       filter("A"."CITY"<>"B"."CITY")
Note
-----
   - dynamic sampling used for this statement (level=2)

（TABLE ACCESS BY USER ROWID）USER ROWID 指的是 ROWID 是用户来提供的，INDEX ROWID 是 索引来提供的rowid