《oracle-sql优化案例》由会员分享，可在线阅读，更多相关《oracle-sql优化案例（39页珍藏版）》请在金锄头文库上搜索。
1、一个哥们 QQ 问我，这个 SQL 怎么优化，它要跑 160 秒 view plaincopy to clipboardprint?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 a.op_id op_id, 6. 6 nvl(c.brand, 0) brand, 7. 7 e.res_code, 8. 8 a.b
2、usi_code, 9. 9 a.so_nbr, 10. 10 decode(a.isnormal, 11. 11 2, 12. 12 -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 sum(decode(b.book_item_id, 23000001, item_total, 0) / 100 si
3、m_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. 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
4、.res_sim e 25. 25 where 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, 31. 31 2, 32. 32 4, 33. 33 5, 34. 34 8, 35. 35 11, 36. 36 14, 37.
5、 37 15, 38. 38 17, 39. 39 18, 40. 40 19, 41. 41 21, 42. 42 24, 43. 43 25, 44. 44 28, 45. 45 99, 46. 46 101, 47. 47 104, 48. 48 105, 49. 49 201, 50. 50 204, 51. 51 205, 52. 52 206, 53. 53 2201, 54. 54 1023, 55. 55 1006, 56. 56 3312, 57. 57 2251) 58. 58 and a.op_id != 71010264 59. 59 and a.so_date = t
6、o_date(20110831000000, yyyymmddhh24miss) 60. 60 and a.so_date 78.SQL select * from table(dbms_xplan.display); 79. 80.PLAN_TABLE_OUTPUT 81.- 82. 83.- 84.| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | 85.- 86.| 0 | SELECT STATEMENT | | 59 | 11741 | 1703 (1)| | | 87.| 1 | SORT GR
7、OUP BY | | 59 | 11741 | 1703 (1)| | | 88.|* 2 | FILTER | | | | | | | 89.|* 3 | HASH JOIN OUTER | | | | | | | 90.| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | RES_SIM | 1 | 26 | 32 (4)| | | 91.| 5 | NESTED LOOPS | | 46 | 7820 | 1670 (1)| | | 92.| 6 | NESTED LOOPS | | 49 | 7056 | 146 (2)| | | 93.| 7 | NESTED LOOPS | | 46 | 5244 | 53 (2)| | | 94.|* 8 | TABLE ACCESS BY INDEX ROWID | CM_BUSI_201108 | 46 | 4784 | 7 (15)| | | 95.|* 9 | INDEX RANGE SCAN | DX_BUSI_SO_DATE_201108 | 166K| | 3 (34)| | | 96.| 10 | TABLE ACCESS BY INDEX ROWID | SYS_ORGANIZATIONS | 1 | 10 |
