一次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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-686878/