FILTER优化

explain plan for  select   a.*
  from fxqd_list_20131115_new_100 a
 where (acct_no, oper_no, seqno, trans_amt) not in
       (select acct_no, oper_no, seqno, trans_amt
          from fxqd_list b
         where reg_date = to_date('20131115', 'yyyymmdd'))
   and list_type = '1';

 
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |    92 |   247K|   191   (0)|
|*  1 |  FILTER                      |                            |       |       |            |
|*  2 |   TABLE ACCESS FULL          | FXQD_LIST_20131115_NEW_100 |    93 |   249K|     5   (0)|
|*  3 |   TABLE ACCESS BY INDEX ROWID| FXQD_LIST                  |  2036 |   157K|     4   (0)|
|*  4 |    INDEX RANGE SCAN          | FXQD_LIST_IDX_1            |  2500 |       |     3   (0)|
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "FXQD_LIST" "B" 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
-----
   - 'PLAN_TABLE' is old version

FILTER就不多讲了,遇到太多次了,直接改写
elect a.*
  from fxqd_list_20131115_new_100 a left join (select acct_no, oper_no, seqno, trans_amt
          from fxqd_list
         where reg_date = to_date('20131115', 'yyyymmdd')) b
on a.acct_no||a.oper_no||a.seqno||a.trans_amt = b.acct_no||b.oper_no||b.seqno||b.trans_amt
where 
    a.list_type = '1'
   and b.acct_no||b.oper_no||b.seqno||b.trans_amt is null


看下是否等价:
 select   a.*
  from fxqd_list_20131115_new_100 a
 where (acct_no, oper_no, seqno, trans_amt) not in
       (select acct_no, oper_no, seqno, trans_amt
          from fxqd_list b
         where reg_date = to_date('20131115', 'yyyymmdd'))
   and list_type = '1'

---返回3条记录


   select a.*
  from fxqd_list_20131115_new_100 a left join (select acct_no, oper_no, seqno, trans_amt
          from fxqd_list
         where reg_date = to_date('20131115', 'yyyymmdd')) b
on a.acct_no||a.oper_no||a.seqno||a.trans_amt = b.acct_no||b.oper_no||b.seqno||b.trans_amt
where 
    a.list_type = '1'
   and b.acct_no||b.oper_no||b.seqno||b.trans_amt is null
----同样返回3条记录

 select   a.*
  from fxqd_list_20131115_new_100 a
 where (acct_no, oper_no, seqno, trans_amt) not in
       (select acct_no, oper_no, seqno, trans_amt
          from fxqd_list b
         where reg_date = to_date('20131115', 'yyyymmdd'))
   and list_type = '1'
   
   minus 
   select a.*
  from fxqd_list_20131115_new_100 a left join (select acct_no, oper_no, seqno, trans_amt
          from fxqd_list
         where reg_date = to_date('20131115', 'yyyymmdd')) b
on a.acct_no||a.oper_no||a.seqno||a.trans_amt = b.acct_no||b.oper_no||b.seqno||b.trans_amt
where 
    a.list_type = '1'
   and b.acct_no||b.oper_no||b.seqno||b.trans_amt is null

完全等价