with as 干掉谓词推入

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'))