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

2022-01-31 22:48:53




    

  1. 以下语句完全正确，但COST较高，原因是因为AEH、AEC、AC、ACSN几张表都有数百万条记录， 
    2. 

  2. 由于采用了HASH连接，尝试采用优化索引等多种方式，但是由于记录过多，表的查询条件相对较少， 
    3. 

  3. COST下降幅度始终很有限，COST提高到1000出头已经很尽力了。 
    4. 

  4. Select Count(Tmp.Id) 
    5. 

  5. From (Select Rownum As Id, t.Entry_Id, t.Container_Id, Con.Container_Num 
    6. 

  6.              From AEC t 
    7. 

  7.              Inner Join AEH Head On t.Entry_Id = Head.Entry_Id 
    8. 

  8.              Inner Join AC Con On t.Container_Id = Con.Id And Head.Deleted_Flag = '0' And Con.Deleted_Flag = '0') Tmp 
    9. 

  9.        Inner Join ACSN Constatus On Tmp.Container_Id = Constatus.Container_Id And 
    10. 

  10.        Constatus.Px_Operation_Status <> '0'  
    11. 

  11.        And Tmp.Entry_Id = :B2  
    12. 

  12.        And Tmp.Container_Num = :B1 
    13. 

  13.  
    14. 

  14. SELECT STATEMENT, GOAL = ALL_ROWS                                               Cost=3519   Cardinality=1       Bytes=62 
    15. 

  15.  SORT AGGREGATE                                                 Cardinality=1       Bytes=62 
    16. 

  16.   MERGE JOIN                                                                    Cost=3519   Cardinality=51      Bytes=3162 
    17. 

  17.    TABLE ACCESS BY INDEX ROWID      Object owner=TEST   Object name=ACSN        Cost=774    Cardinality=49      Bytes=343 
    18. 

  18.     INDEX FULL SCAN                 Object owner=TEST   Object name=FK_A_C_C_S_P    Cost=6      Cardinality=1849     
    19. 

  19.    SORT JOIN                                                                    Cost=2745   Cardinality=1470    Bytes=80850 
    20. 

  20.     VIEW                            Object owner=TEST                           Cost=2744   Cardinality=1470    Bytes=80850 
    21. 

  21.      COUNT                   
    22. 

  22.       HASH JOIN                                                                 Cost=2744   Cardinality=1470    Bytes=94080 
    23. 

  23.        NESTED LOOPS                                                             Cost=1494   Cardinality=1470    Bytes=64680 
    24. 

  24.         INDEX FAST FULL SCAN        Object owner=TEST   Object name=IDX_A_E_C   Cost=5      Cardinality=1470    Bytes=33810 
    25. 

  25.         TABLE ACCESS BY INDEX ROWID Object owner=TEST   Object name=AEH         Cost=2      Cardinality=1       Bytes=21 
    26. 

  26.          INDEX UNIQUE SCAN          Object owner=TEST   Object name=PK_AEH      Cost=1      Cardinality=1    
    27. 

  27.        TABLE ACCESS FULL            Object owner=TEST   Object name=A_CO        Cost=1220   Cardinality=1005299 Bytes=20105980 
    28. 

  28.  
    29. 

  29.  
    30. 

  30. 但再细看可以发现有如下苗头： 
    31. 

  31.        And Tmp.Entry_Id = :B2  
    32. 

  32.        And Tmp.Container_Num = :B1 
    33. 

  33. 两个条件是等AEC、AEH、AC三个表HASH连接后才做的过滤，为啥不在连接前就对表做过滤， 
    34. 

  34. 再一查在AEH表中有Entry_Id字段，而Container_Num是AC中的一个字段，于是把这两个 
    35. 

  35. 条件从对TMP的外层过滤移入到AEH、AC内层过滤中，语句如下； 
    36. 

  36.  
    37. 

  37. Select Count(Tmp.Id) 
    38. 

  38. From (Select Rownum As Id, t.Entry_Id, t.Container_Id, Con.Container_Num 
    39. 

  39.              From AEC t 
    40. 

  40.              Inner Join AEH Head On t.Entry_Id = Head.Entry_Id And Head.Entry_Id = :B2 --迁移条件Head.Entry_Id = :B2到此处 
    41. 

  41.              Inner Join AC Con On t.Container_Id = Con.Id And Head.Deleted_Flag = '0' And Con.Deleted_Flag = '0'  
    42. 

  42.              And con.Container_Num = :B1) Tmp   --迁移条件con.Container_Num = :B1到此处 
    43. 

  43.        Inner Join ACSN Constatus On Tmp.Container_Id = Constatus.Container_Id And 
    44. 

  44.        Constatus.Px_Operation_Status <> '0'  
    45. 

  45.        --And Tmp.Entry_Id = :B2  
    46. 

  46.        --And Tmp.Container_Num = :B1 
    47. 

  47.  
    48. 

  48. 实测结果表明，COST已经下降到了9，提升近500倍，相信这个效果是大家乐意接受的。 
    49. 

  49.  
    50. 

  50. SELECT STATEMENT, GOAL = ALL_ROWS                                 Cost=9  Cardinality=1   Bytes=33 
    51. 

  51.  SORT AGGREGATE                                                                             Cardinality=1   Bytes=33 
    52. 

  52.   NESTED LOOPS                                                                      Cost=9  Cardinality=1   Bytes=33 
    53. 

  53.    VIEW                             Object owner=TEST                                                           Cost=7  Cardinality=1   Bytes=26 
    54. 

  54.     COUNT                    
    55. 

  55.      NESTED LOOPS                                                                   Cost=7  Cardinality=1   Bytes=64 
    56. 

  56.       NESTED LOOPS                                                                  Cost=5  Cardinality=1   Bytes=44 
    57. 

  57.        TABLE ACCESS BY INDEX ROWID  Object owner=TEST   Object name=AEH             Cost=3  Cardinality=1   Bytes=21 
    58. 

  58.         INDEX UNIQUE SCAN           Object owner=TEST   Object name=PK_AEH          Cost=2  Cardinality=1    
    59. 

  59.        INDEX RANGE SCAN             Object owner=TEST   Object name=IDX_A_E_C       Cost=2  Cardinality=1   Bytes=23 
    60. 

  60.       TABLE ACCESS BY INDEX ROWID   Object owner=TEST   Object name=AC              Cost=2  Cardinality=1   Bytes=20 
    61. 

  61.        INDEX UNIQUE SCAN            Object owner=TEST   Object name=IDX_C           Cost=1  Cardinality=1    
    62. 

  62.    TABLE ACCESS BY INDEX ROWID      Object owner=TEST   Object name=ACSN            Cost=2  Cardinality=1   Bytes=7 
    63. 

  63.     INDEX UNIQUE SCAN               Object owner=TEST   Object name=FK_A_C_C_S_P    Cost=1  Cardinality=1    
    64. 



本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/574394，如需转载请自行联系原作者


上一篇：【COCOS CREATOR 系列教程之四】基于0.7.1先简单制作一个PAGEVIEW

下一篇：SAP CDS view自学教程之六：如何在CDS view里消费table function