资源描述:
《oracle sql优化案例》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
1、一个哥们QQ问我,这个SQL怎么优化,它要跑160秒 viewplaincopytoclipboardprint?1.SQL> explain plan for select a.so_region_code so_region_code, 2. 2 a.so_county_code so_county_code, 3. 3 a.so_org_id so_org_id, 4. 4 d.org_type_id org_type_id, 5. 5
2、 a.op_id op_id, 6. 6 nvl(c.brand, 0) brand, 7. 7 e.res_code, 8. 8 a.busi_code, 9. 9 a.so_nbr, 10. 10 decode(a.isnormal, 11. 11 2, 12. 12
3、 -count(distinct a.so_nbr), 13. 13 0, 14. 14 count(distinct a.so_nbr), 15. 15 0) so_amount, 16. 16 sum(decode(b.book_item_id, 23000002, item_total, 0)) / 100 sim_fee, 17. 17
4、 sum(decode(b.book_item_id, 23000001, item_total, 0)) / 100 sim_fee_add, 18. 18 sum(decode(b.book_item_id, 27000003, item_total, 0)) / 100 sim_fee_discount, 19. 19 sum(decode(b.book_item_id, 21000013, 0, b.item_total)) / 100 total_fee 20
5、. 20 from zk.cm_busi_201108 a, 21. 21 zk.cm_busi_charge_201108 b, 22. 22 zk.cm_user c, 23. 23 xg.sys_organizations d, 24. 24 zy.res_sim e 25. 25 where
6、a.so_nbr = b.so_nbr(+) 26. 26 and a.serv_id = c.serv_id 27. 27 and c.sim_id = e.sim_id 28. 28 and a.so_org_id = d.org_id 29. 29 and (b.book_item_id in (23000001, 23000002, 27000003) or 30. 30 a.busi_code in (1,
7、 31. 31 2, 32. 32 4, 33. 33 5, 34. 34 8, 35. 35 11, 36. 36 14, 37. 37
8、 15, 38. 38