SQL优化中查询条件内移及减少HASH JOIN的代价

以下语句完全正确，但COST较高，原因是因为AEH、AEC、AC、ACSN几张表都有数百万条记录，    
由于采用了HASH连接，尝试采用优化索引等多种方式，但是由于记录过多，表的查询条件相对较少，    
COST下降幅度始终很有限，COST提高到1000出头已经很尽力了。    
Select Count(Tmp.Id)    
From (Select Rownum As Id, t.Entry_Id, t.Container_Id, Con.Container_Num    
             From AEC t    
             Inner Join AEH Head On t.Entry_Id = Head.Entry_Id    
             Inner Join AC Con On t.Container_Id = Con.Id And Head.Deleted_Flag = '0' And Con.Deleted_Flag = '0') Tmp    
       Inner Join ACSN Constatus On Tmp.Container_Id = Constatus.Container_Id And
       Constatus.Px_Operation_Status <> '0' 
       And Tmp.Entry_Id = :B2     
       And Tmp.Container_Num = :B1    
        
SELECT STATEMENT, GOAL = ALL_ROWS                                Cost=3519   Cardinality=1       Bytes=62    
 SORT AGGREGATE                                                 Cardinality=1       Bytes=62    
  MERGE JOIN                                             Cost=3519   Cardinality=51      Bytes=3162    
   TABLE ACCESS BY INDEX ROWID      Object owner=TEST   Object name=ACSN        Cost=774    Cardinality=49      Bytes=343    
    INDEX FULL SCAN          Object owner=TEST   Object name=FK_A_C_C_S_P    Cost=6      Cardinality=1849        
   SORT JOIN                                             Cost=2745   Cardinality=1470    Bytes=80850    
    VIEW                 Object owner=TEST                    Cost=2744   Cardinality=1470    Bytes=80850    
     COUNT                  
      HASH JOIN                                          Cost=2744   Cardinality=1470    Bytes=94080    
       NESTED LOOPS                                      Cost=1494   Cardinality=1470    Bytes=64680    
        INDEX FAST FULL SCAN        Object owner=TEST   Object name=IDX_A_E_C   Cost=5      Cardinality=1470    Bytes=33810    
        TABLE ACCESS BY INDEX ROWID Object owner=TEST   Object name=AEH         Cost=2      Cardinality=1       Bytes=21    
         INDEX UNIQUE SCAN       Object owner=TEST   Object name=PK_AEH      Cost=1      Cardinality=1       
       TABLE ACCESS FULL         Object owner=TEST   Object name=A_CO        Cost=1220   Cardinality=1005299 Bytes=20105980    
        
        
但再细看可以发现有如下苗头：    
       And Tmp.Entry_Id = :B2     
       And Tmp.Container_Num = :B1    
两个条件是等AEC、AEH、AC三个表HASH连接后才做的过滤，为啥不在连接前就对表做过滤，    
再一查在AEH表中有Entry_Id字段，而Container_Num是AC中的一个字段，于是把这两个    
条件从对TMP的外层过滤移入到AEH、AC内层过滤中，语句如下；    
        
Select Count(Tmp.Id)    
From (Select Rownum As Id, t.Entry_Id, t.Container_Id, Con.Container_Num    
             From AEC t    
             Inner Join AEH Head On t.Entry_Id = Head.Entry_Id And Head.Entry_Id = :B2 --迁移条件Head.Entry_Id = :B2到此处    
             Inner Join AC Con On t.Container_Id = Con.Id And Head.Deleted_Flag = '0' And Con.Deleted_Flag = '0' 
             And con.Container_Num = :B1) Tmp   --迁移条件con.Container_Num = :B1到此处    
       Inner Join ACSN Constatus On Tmp.Container_Id = Constatus.Container_Id And
       Constatus.Px_Operation_Status <> '0' 
       --And Tmp.Entry_Id = :B2     
       --And Tmp.Container_Num = :B1    
        
实测结果表明，COST已经下降到了9，提升近500倍，相信这个效果是大家乐意接受的。    
        
SELECT STATEMENT, GOAL = ALL_ROWS                                Cost=9  Cardinality=1   Bytes=33    
 SORT AGGREGATE                                              Cardinality=1   Bytes=33    
  NESTED LOOPS                                           Cost=9  Cardinality=1   Bytes=33    
   VIEW  Object owner=TEST                                    Cost=7  Cardinality=1   Bytes=26    
    COUNT                   
     NESTED LOOPS                                        Cost=7  Cardinality=1   Bytes=64    
      NESTED LOOPS                                       Cost=5  Cardinality=1   Bytes=44    
       TABLE ACCESS BY INDEX ROWID  Object owner=TEST   Object name=AEH          Cost=3  Cardinality=1   Bytes=21    
        INDEX UNIQUE SCAN        Object owner=TEST   Object name=PK_AEH       Cost=2  Cardinality=1       
       INDEX RANGE SCAN          Object owner=TEST   Object name=IDX_A_E_C       Cost=2  Cardinality=1   Bytes=23    
      TABLE ACCESS BY INDEX ROWID   Object owner=TEST   Object name=AC           Cost=2  Cardinality=1   Bytes=20    
       INDEX UNIQUE SCAN         Object owner=TEST   Object name=IDX_C        Cost=1  Cardinality=1       
   TABLE ACCESS BY INDEX ROWID      Object owner=TEST   Object name=ACSN         Cost=2  Cardinality=1   Bytes=7    
    INDEX UNIQUE SCAN            Object owner=TEST   Object name=FK_A_C_C_S_P    Cost=1  Cardinality=1

转载请注明：http://www.xcqg58.com/bcxx/bcxx/36.html