一个哥们 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.busi_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 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. 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 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. 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 = to_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 GROUP 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 |