一次HASH JION過慢優化（1）

原創 轉載請註明出處

最近我發現生產有一個語句執行比較慢。需要4-5分鐘。所以對其進行了優化，優化結果執行只需要不到3秒。
語句如下：
我發現出問題的部分是
select *
                  from (select a.test,
                               a.test1,
                               a.test2,
                               a.test3,
                               a.test4,
                               case
                                 when b.test5 = '1' then
                                  b.test6
                                 else
                                  a.test6
                               end test6,
                               0 bankComm,
                               c.test7 || '-' || case
                                 when c.test8= '1' then
                                  '收'
                                 when c.test8= '2' then
                                  '付'
                               end payway,
                               case
                                 when a.poatype = '1' then
                                  a.poainfo
                                 else
                                  ''
                               end,
                               a.test9,
                               b.test10,
                               b.test11,
                               a.test12,
                               a.test13,
                               a.test14,
                               case
                                 when a.test15 = b.test15 then
                                  a.test19
                                 else
                                  a.totest19
                               end,
                               b.totest19,
                               a.totest19,
                               a.totest19
                          from prod.totest19 a,
                               prod.totest19 b,
                               prod.totest19        c
                         where (a.totest15 = b.test15 or
                               a.test15 = b.test15)
                           and b.totest19 not in ('50', '51', '60', '61')
                           and c.totest19 = '1'
                           and c.totest19 = '1'
                           and b.totest19 = c.paywaycode
                           and b.totest19<> '212'
                           AND to_char(a.test, 'yyyy-mm-dd') >=
                               '2011-01-11'
                           AND to_char(a.test, 'yyyy-mm-dd') <=
                               '2011-01-12'
執行計劃如下：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3443708996
--------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes |TempSpc| Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   808 |   183K|       |  775
|*  1 |  HASH JOIN            |                   |   808 |   183K|  2648K|  775
|   2 |   MERGE JOIN CARTESIAN|                   | 13655 |  2480K|       |  677
|*  3 |    TABLE ACCESS FULL  | test3|   846 |   128K|       |  584
|   4 |    BUFFER SORT        |                   |    16 |   480 |       |   93
|*  5 |     TABLE ACCESS FULL | test2       |    16 |   480 |       |
|*  6 |   TABLE ACCESS FULL   | test | 46215 |  2121K|       |   51

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-686878/，如需轉載，請註明出處，否則將追究法律責任。