query conditions in SQL optimization and reduce the cost of hash join

The following statement is completely correct, but the cost is high, because Aeh, AEC, AC, ACSN several tables have millions of records, because of the use of hash connection, try to use the optimization index and many other ways, but due to too many records, table query conditions are relatively small, the cost of the decline is always very limited, C    
OST has been doing its best in the early 1000. Select Count (tmp.id) from (select RowNum as Id, t.entry_id, t.container_id, con.container_num from AE C T Inner join Aeh head in t.entry_id = head.entry_id Inner join AC Con on T.container_i D = con.id and Head.deleted_flag = ' 0 ' and Con.deleted_flag = ' 0 ') Tmp Inner Join ACSN constatus on Tmp.contain     
       er_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 byt  es=62 MERGE JOIN cost=3519 cardinality=51 bytes=3162 TABLEACCESS by INDEX ROWID Object Owner=test object NAME=ACSN cost=774 cardinality=49 bytes=343 I                                             Ndex 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 objec T owner=test cost=2744 cardinality=1470 bytes=80850 COUNT HASH JO                                      In cost=2744 cardinality=1470 bytes=94080 NESTED LOOPS cost=1494 cardinality=1470 bytes=64680 INDEX FAST full SCAN Object owne R=test Object name=idx_a_e_c cost=5 cardinality=1470 bytes=33810 TABLE ACCESS by INDEX ROWID objec T owner=test Object name=aeh cost=2 cardinality=1 bytes=21 INDEX UNIQUE SCAN objec T 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 But a closer look can be found as follows: and tmp.entry_id =: B2 and Tmp.container_num =: B1 Two conditions are such as AEC, Aeh, ac three table hash after the filter is done, why not before the connection to the table to filter, and then a check in the Aeh table has entry_id fields, and Contai The Ner_num is a field in AC, so the two conditions are moved from the outer layer of TMP to the Aeh, the AC inner layer filter, and the statements are as follows; Select Count (Tmp.id) from (select RowNum as IDs, t.entry_id, t.container_id, con.container_num from AEC t Inner Join aeh head on T.en try_id = head.entry_id and head.entry_id =: B2--Migration condition head.entry_id =: B2 to here Inner Join AC Con on T.contai ner_id = con.id and Head.deleted_flag = ' 0 ' and Con.deleted_flag = ' 0 ' and Con. Container_num =: B1) Tmp--Migration condition con. Container_num =: B1 to here Inner Join ACSN constatus on tmp.container_id = constatus.container_id and Consta Tus. Px_operation_status <> ' 0 '--and tmp.entry_id =: B2--and tmp.container_num =: B1 The measured results show that the cost has been    
        
Down to 9, to improve nearly 500 times times, I believe this effect is acceptable to everyone.                                              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 cardinalit Y=1 bytes=26 COUNT NESTED LOOPS cost=7 Cardinali Ty=1 bytes=64 NESTED LOOPS cost=5 cardinality=1 bytes=44 T Able ACCESS by INDEX ROWID Object Owner=test object Name=aeh cost=3 cardinality=1 bytes=21 Inde X 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 UNI      QUE 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 Obj ECT owner=test Object name=fk_a_c_c_s_p cost=1 Cardinality=1