- 以下语句完全正确，但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
本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/574394，如需转载请自行联系原作者
