原创 转载请注明出处
最近我发现生产有一个语句执行比较慢。需要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
