SQL> explain plan for select *

  from fxqd_list_20131115_new

 where (acct_no, oper_no, seqno, trans_amt) not in

       (select acct_no, oper_no, seqno, trans_amt

          from fxqd_list

         where reg_date = to_date('20131115', 'yyyymmdd'))

   and list_type = '1';  2    3    4    5    6    7  

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 196590019

-------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                        |  4145 |    10M| 13079   (1)| 00:02:37 |

|*  1 |  FILTER                      |                        |       |       |            |          |

|*  2 |   TABLE ACCESS FULL          | FXQD_LIST_20131115_NEW |  4145 |    10M|   180   (2)| 00:00:03 |

|*  3 |   TABLE ACCESS BY INDEX ROWID| FXQD_LIST              |  2036 |   157K|     4   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN          | FXQD_LIST_IDX_1        |  2500 |       |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "FXQD_LIST" "FXQD_LIST" WHERE

              "REG_DATE"=TO_DATE(' 2013-11-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND LNNVL("ACCT_NO"<>:B1)

              AND LNNVL("OPER_NO"<>:B2) AND LNNVL("SEQNO"<>:B3) AND LNNVL("TRANS_AMT"<>:B4)))

   2 - filter("LIST_TYPE"='1')

   3 - filter(LNNVL("ACCT_NO"<>:B1) AND LNNVL("OPER_NO"<>:B2) AND LNNVL("SEQNO"<>:B3) AND

              LNNVL("TRANS_AMT"<>:B4))

   4 - access("REG_DATE"=TO_DATE(' 2013-11-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note

-----

   - dynamic sampling used for this statement

26 rows selected.

查看特殊执行计划:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fg7h5y1451z61, child number 0

-------------------------------------

select *   from fxqd_list_20131115_new  where (acct_no, oper_no, seqno, trans_amt) not in        (select acct_no,

oper_no, seqno, trans_amt           from fxqd_list          where reg_date = to_date('20131115', 'yyyymmdd'))

and list_type = '1'

Plan hash value: 196590019

--------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

--------------------------------------------------------------------------------------------------------------------------

|*  1 |  FILTER                      |                        |      1 |        |    103 |00:00:32.10 |    1563K|    225 |

|*  2 |   TABLE ACCESS FULL          | FXQD_LIST_20131115_NEW |      1 |   4145 |   4159 |00:00:00.13 |     795 |    225 |

|*  3 |   TABLE ACCESS BY INDEX ROWID| FXQD_LIST              |   4159 |   2036 |   4056 |00:00:31.95 |    1562K|      0 |

|*  4 |    INDEX RANGE SCAN          | FXQD_LIST_IDX_1        |   4159 |   2500 |     14M|00:00:03.55 |   67954 |      0 |

--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter( IS NULL)

   2 - filter("LIST_TYPE"='1')

   3 - filter((LNNVL("ACCT_NO"<>:B1) AND LNNVL("OPER_NO"<>:B2) AND LNNVL("SEQNO"<>:B3) AND

              LNNVL("TRANS_AMT"<>:B4)))

   4 - access("REG_DATE"=TO_DATE(' 2013-11-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note

-----

   - dynamic sampling used for this statement

30 rows selected.

利用with as 改写:

SQL> with D as (select  /*+ materialize*/ acct_no, oper_no, seqno, trans_amt

          from fxqd_list

         where reg_date = to_date('20131115', 'yyyymmdd'))

   select * from fxqd_list_20131115_new where (acct_no, oper_no, seqno, trans_amt) not in

   (select acct_no, oper_no, seqno, trans_amt from D)

   and list_type = '1';  2    3    4    5    6  

103 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2773456887

-------------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                             |  4145 |    10M| 26570   (1)| 00:05:19 |

|   1 |  TEMP TABLE TRANSFORMATION    |                             |       |       |            |          |

|   2 |   LOAD AS SELECT              | FXQD_LIST_20131115_NEW      |       |       |            |          |

|   3 |    TABLE ACCESS BY INDEX ROWID| FXQD_LIST                   |  2500 |   192K|   389   (1)| 00:00:05 |

|*  4 |     INDEX RANGE SCAN          | FXQD_LIST_IDX_1             |  2500 |       |    10   (0)| 00:00:01 |

|*  5 |   FILTER                      |                             |       |       |            |          |

|*  6 |    TABLE ACCESS FULL          | FXQD_LIST_20131115_NEW      |  4145 |    10M|   180   (2)| 00:00:03 |

|*  7 |    VIEW                       |                             |  2500 |   170K|     8   (0)| 00:00:01 |

|   8 |     TABLE ACCESS FULL         | SYS_TEMP_0FD9D66DA_A4F53A1D |  2500 |   170K|     8   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("REG_DATE"=TO_DATE(' 2013-11-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

   5 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"

              "ACCT_NO","C1" "OPER_NO","C2" "SEQNO","C3" "TRANS_AMT" FROM "SYS"."SYS_TEMP_0FD9D66DA_A4F53A1D"

              "T1") "D" WHERE LNNVL("ACCT_NO"<>:B1) AND LNNVL("OPER_NO"<>:B2) AND LNNVL("SEQNO"<>:B3) AND

              LNNVL("TRANS_AMT"<>:B4)))

   6 - filter("LIST_TYPE"='1')

   7 - filter(LNNVL("ACCT_NO"<>:B1) AND LNNVL("OPER_NO"<>:B2) AND LNNVL("SEQNO"<>:B3) AND

              LNNVL("TRANS_AMT"<>:B4))

Note

-----

   - dynamic sampling used for this statement

Statistics

----------------------------------------------------------

        171  recursive calls

         64  db block gets

     123638  consistent gets

         55  physical reads

       1556  redo size

      43694  bytes sent via SQL*Net to client

        558  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        103  rows processed

如果不加/*+ materialize*/ hints呢？

SQL> with D as (select   acct_no, oper_no, seqno, trans_amt

          from fxqd_list

         where reg_date = to_date('20131115', 'yyyymmdd'))

   select * from fxqd_list_20131115_new where (acct_no, oper_no, seqno, trans_amt) not in

   (select acct_no, oper_no, seqno, trans_amt from D)

   and list_type = '1';  2    3    4    5    6  

103 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 196590019

-------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                        |  4145 |    10M| 13079   (1)| 00:02:37 |

|*  1 |  FILTER                      |                        |       |       |            |          |

|*  2 |   TABLE ACCESS FULL          | FXQD_LIST_20131115_NEW |  4145 |    10M|   180   (2)| 00:00:03 |

|*  3 |   TABLE ACCESS BY INDEX ROWID| FXQD_LIST              |  2036 |   157K|     4   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN          | FXQD_LIST_IDX_1        |  2500 |       |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "FXQD_LIST" "FXQD_LIST" WHERE

              "REG_DATE"=TO_DATE(' 2013-11-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND LNNVL("ACCT_NO"<>:B1)

              AND LNNVL("OPER_NO"<>:B2) AND LNNVL("SEQNO"<>:B3) AND LNNVL("TRANS_AMT"<>:B4)))

   2 - filter("LIST_TYPE"='1')

   3 - filter(LNNVL("ACCT_NO"<>:B1) AND LNNVL("OPER_NO"<>:B2) AND LNNVL("SEQNO"<>:B3) AND

              LNNVL("TRANS_AMT"<>:B4))

   4 - access("REG_DATE"=TO_DATE(' 2013-11-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note

-----

   - dynamic sampling used for this statement

Statistics

----------------------------------------------------------

         19  recursive calls

          0  db block gets

    1564063  consistent gets

          0  physical reads

          0  redo size

      43694  bytes sent via SQL*Net to client

        558  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        103  rows processed

最新文章

1. JTree使用
2. apache(nginx)+django+virutalenv(virtualenvwrapper)+gunicorn+supervisor配置高效web环境
3. MVC部分视图的巧用
4. C语言中链表怎么删除结点？
5. ACM/ICPC 之 数据结构-线段树+区间离散化(POJ2528)
6. Swift字符与字符串
7. 设置Oracle 12C OEM 端口
8. oracle表分析
9. c#求slope线性回归斜率
10. Qt wrappers for OS X Cocoa widgets
11. Android 创建自定义布局
12. Kafka学习-Producer和Customer
13. 在 iOS 10.0 之后, App 要调用手机相机与相簿应注意的事项
14. 使用react-router实现单页面应用路由
15. HTML5和CSS3的新特性
16. Flask之SQLAlchemy,flask_session以及蓝图
17. 利用jQuery-UI和jsPlumb实现拖拽连接模型
18. 版本控制系统 git 之基础讲解
19. 学习笔记之Data Visualization
20. 关于ueditor与arcgis js api同用会报错的问题

热门文章

1. Chrome浏览器离线下载地址（Stable/Beta/Dev）
2. 免费的手机号码归属地查询API接口文档
3. 11.9 noip模拟试题
4. 如何打包成jar包自己看呢？
5. Java SE (6)之 多线程
6. Android Studio中常用插件及浅释
7. Web.xml配置详解之context-param （加载spring的xml，然后初始化bean看的）
8. CALayer 认识
9. IE6 兼容问题总结
10. [LeetCode OJ] Sort Colors