1015_sql优化（delete）
来源：互联网 发布：独角兽全武装数据 编辑：程序博客网 时间：2024/06/05 22:40
SQL> set linesize 200SQL> set pagesize 0SQL> 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>
