旧系统中的一条sql在某些情况下无法工作(反应超慢), 此sql是使用关联进行
行列互换, 从执行计划和重建索引都查不出原因所在, 10G已经对sql不挑剔了, 晕啊(周五晚上的时间就这样浪费掉)
1
select table2.id,
2 NVL(t1.count, 0) as count1,
3 NVL(t2.count, 0) as count2,
4 NVL(t3.count, 0) as count3,
5 NVL(t4.count, 0) as count4
6 from table1 t1, table1 t2, table1 t3, table1 t4, table2
7 where t1.shop_id = ? and t1.type = 'a'
8 and t2.shop_id = ? and t2.type = 'b'
9 and t3.shop_id = ? and t3.type = 'c'
10 and t4.shop_id = ? and t4.type = 'd'
11 and table2.id = t1.id
12 and table2.id = t2.id
13 and table2.id = t3.id
14 and table2.id = t4.id
15 order by table2.code shop_id = 1386时很快(0.3s), shop_id = 1404时超慢(15s以上), 时间是在pl/sql developer执行时计算的, 不知是否oracle的bug
只能对sql进行优化, 但一时不知如何解决(本人较笨), 只有发挥copy paste的能力(找google),
http://www.oracle.com.cn/viewthread.php?tid=65638
select t1.id,
sum(decode(t1.type, 'a', count, 0)) count1,
sum(decode(t1.type, 'b', count, 0)) count2,
sum(decode(t1.type, 'c', count, 0)) count3,
sum(decode(t1.type, 'd', count, 0)) count4
from table1 t1, table2 t2
where t1.id = t2.id
and t1.shop_id = ?
group by t1.id
order by max(t2.code) 执行时间0.17s(有点兴奋, 程序员只有这点好处)
还有通过SP进行行列互换的, 可到itpub上找找