舊系統中的一條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上找找