time 
设为首页】【收藏本站
当前位置: 主页 > 数据库 > 数据库理论 > SQL优化一例:查询条件内移,减少HASH JOIN代价

SQL优化一例:查询条件内移,减少HASH JOIN代价

时间:2011-06-08 22:59 点击:2419次 字体:[ ]




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

 



本文地址 : http://www.fengfly.com/plus/view-201343-1.html
标签: 查询 优化 SQL Join hash 内移 一例 代价 减少 条件
------分隔线----------------------------
相关文章
最新评论 查看所有评论
发表评论 查看所有评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码: