SQL> explain plan for select count(1)
2 from v48_transaction_model m
where 1 = 1
and m.trandate = date'2017-01-15'
and 1 > 0
/*and m.dsacctname like :3*/
and 1 > 0
and 1 > 0
and not exists (select 1
from t00_user_info t, t48_party p, v48_account a
where t.idcardnumber = p.ca10crdid
and p.ca10cno = a.ca10cno
and m.acctnum = a.ad10acno)
and 1 > 0 3 4 5 6 7 8 9 10 11 12 13 14 ;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3361881582
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 147 (1)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 29 | | | | |
| 2 | NESTED LOOPS ANTI | | 1 | 29 | 147 (1)| 00:00:02 | | |
| 3 | PARTITION RANGE SINGLE | | 1 | 27 | 2 (0)| 00:00:01 | 2208 | 2208 |
|* 4 | TABLE ACCESS FULL | T48_TRANSACTION_MODEL | 1 | 27 | 2 (0)| 00:00:01 | 2208 | 2208 |
| 5 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 2 | 145 (1)| 00:00:02 | | |
|* 6 | HASH JOIN | | 1 | 81 | 145 (1)| 00:00:02 | | |
| 7 | NESTED LOOPS | | | | | | | |
| 8 | NESTED LOOPS | | 1 | 62 | 8 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS BY INDEX ROWID| V48_ACCOUNT | 1 | 32 | 5 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | IDX_V48_ACCOUNT_ATC | 1 | | 4 (0)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | T48_PARTY_CA10CNO | 1 | | 2 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS BY INDEX ROWID | T48_PARTY | 1 | 30 | 3 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS FULL | T00_USER_INFO | 7320 | 135K| 136 (0)| 00:00:02 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("TRANDATE"=TO_DATE(' 2017-01-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
6 - access("T"."IDCARDNUMBER"="P"."CA10CRDID")
10 - access("A"."AD10ACNO"="ACCTNUM")
11 - access("P"."CA10CNO"="A"."CA10CNO")
28 rows selected.
select count(*) from v48_transaction_model m where m.trandate = date'2017-01-15'
--623240
| 5 | VIEW PUSHED PREDICATE | VW_SQ_1
要把 623240 推入到视图
这里的关联顺序为V48_ACCOUNT 和 T48_PARTY_CA10CNO 就是a和p表关联的结果在和p关联 在和t关联,从而产生一个视图
改写成with as:
explain plan for WITH D AS ( select /*+ materialize*/ a.* from t00_user_info t, t48_party p, v48_account a
where t.idcardnumber = p.ca10crdid
and p.ca10cno = a.ca10cno)
select count(1)
from v48_transaction_model m
where 1 = 1
and m.trandate = date '2017-01-15'
and 1 > 0
/*and m.dsacctname like :3*/
and 1 > 0
and 1 > 0
and not exists (select 1
from D a
where m.acctnum = a.ad10acno)
and 1 > 0;
Plan hash value: 3227919156
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 63005 (1)| 00:12:37 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6772_C5B66925 | | | | | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 26160 | 4445K| 62832 (1)| 00:12:34 | | |
|* 5 | HASH JOIN | | 7320 | 350K| 27206 (1)| 00:05:27 | | |
| 6 | TABLE ACCESS FULL | T00_USER_INFO | 7320 | 135K| 136 (0)| 00:00:02 | | |
| 7 | TABLE ACCESS FULL | T48_PARTY | 4035K| 115M| 27049 (1)| 00:05:25 | | |
|* 8 | INDEX RANGE SCAN | PK_V48_ACCOUNT_CA10CNO | 4 | | 2 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS BY INDEX ROWID| V48_ACCOUNT | 4 | 500 | 6 (0)| 00:00:01 | | |
| 10 | SORT AGGREGATE | | 1 | 61 | | | | |
|* 11 | HASH JOIN ANTI | | 1 | 61 | 172 (1)| 00:00:03 | | |
| 12 | PARTITION RANGE SINGLE | | 1 | 27 | 2 (0)| 00:00:01 | 2208 | 2208 |
|* 13 | TABLE ACCESS FULL | T48_TRANSACTION_MODEL | 1 | 27 | 2 (0)| 00:00:01 | 2208 | 2208 |
| 14 | VIEW | | 26160 | 868K| 170 (1)| 00:00:03 | | |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6772_C5B66925 | 26160 | 3321K| 170 (1)| 00:00:03 | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T"."IDCARDNUMBER"="P"."CA10CRDID")
8 - access("P"."CA10CNO"="A"."CA10CNO")
11 - access("ACCTNUM"="A"."AD10ACNO")
13 - filter("TRANDATE"=TO_DATE(' 2017-01-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))