1015_sql优化（delete）

SQL> set linesize 200
SQL> set pagesize 0
SQL> explain plan for DELETE FROM "xxx".tb1_6500 WHERE idv NOT IN (SELECT idv FROM bak.idv);
Explained.

SQL> select * from table(dbms_xplan.display);
Plan hash value: 4113825579

---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT     |                    |    46M|  2609M|       |   129K  (1)| 00:25:53 |
|   1 |  DELETE              | tb1_6500           |       |       |       |            |          |
|   2 |   MERGE JOIN ANTI NA |                    |    46M|  2609M|       |   129K  (1)| 00:25:53 |
|   3 |    SORT JOIN         |                    |    46M|  2167M|       |   128K  (1)| 00:25:37 |
|   4 |     INDEX FULL SCAN  | IND_DGSN_idv       |    46M|  2167M|       |   128K  (1)| 00:25:37 |
|*  5 |    SORT UNIQUE       |                    |   296K|  2894K|  9320K|  1370   (2)| 00:00:17 |
|   6 |     TABLE ACCESS FULL| idv                |   296K|  2894K|       |   173   (2)| 00:00:03 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("idv"="idv")
       filter("idv"="idv")

19 rows selected.

SQL>  explain plan for DELETE FROM "xxx".tb1_6500 a WHERE exists  (SELECT 1 FROM bak.idv_delte b where a.idv=b.idv);

Explained.

SQL> select * from table(dbms_xplan.display);
Plan hash value: 2299809344

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT     |                 |    14M|   806M|   195K  (2)| 00:39:02 |
|   1 |  DELETE              | tb1_6500        |       |       |            |          |
|*  2 |   HASH JOIN          |                 |    14M|   806M|   195K  (2)| 00:39:02 |
|   3 |    SORT UNIQUE       |                 |    17M|   150M|  9570   (3)| 00:01:55 |
|   4 |     TABLE ACCESS FULL| idv_DELTE       |    17M|   150M|  9570   (3)| 00:01:55 |
|   5 |    TABLE ACCESS FULL | tb1_6500        |    46M|  2167M|   117K  (2)| 00:23:35 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."idv"="B"."idv")

17 rows selected.

SQL>   explain plan for DELETE FROM "xxx".tb1_6500 a WHERE exists  (SELECT 1 FROM bak.idv_delte_1013 b where a.idv=b.idv);

Explained.

SQL> select * from table(dbms_xplan.display);
Plan hash value: 82079763

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |                      |    14M|   800M|   118K  (2)| 00:23:39 |
|   1 |  DELETE               | tb1_6500             |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|                      |    14M|   800M|   118K  (2)| 00:23:39 |
|   3 |    TABLE ACCESS FULL  | idv_DELTE_1013       | 90361 |   794K|    54   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | tb1_6500             |    46M|  2167M|   117K  (2)| 00:23:35 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."idv"="B"."idv")

16 rows selected.

SQL> 
SQL>