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