oracle-sql優(yōu)化案例

1、一個(gè)哥們QQ問我，這個(gè)SQL怎么優(yōu)化，它要跑160秒viewplaincopytoclipboardprint1.SQLexplainplanfa.so_region_codeso_region_code2.2a.so_county_codeso_county_code3.3a.so_g_idso_g_id4.4d.g_type_idg_type_id5.5a.op_idop_id6.6nvl(c.br0)br7.7e.res_code

2、8.8a.busi_code9.9a.so_nbr10.10decode(a.isnmal11.11212.12count(distincta.so_nbr)13.13014.14count(distincta.so_nbr)15.150)so_amount16.16sum(decode(b.book_item_id23000002item_total0))100sim_fee17.17sum(decode(b.book_item_id

3、23000001item_total0))100sim_fee_add18.18sum(decode(b.book_item_id27000003item_total0))100sim_fee_discount19.19sum(decode(b.book_item_id210000130b.item_total))100total_fee20.20fromzk.cm_busi_201108a21.21zk.cm_busi_ge_2011

4、08b22.22zk.cm_userc23.23xg.sys_ganizationsd24.24zy.res_sime25.25wherea.so_nbr=b.so_nbr()26.26a.serv_id=c.serv_id27.27c.sim_id=e.sim_id28.28a.so_g_id=d.g_id29.29(b.book_item_idin(230000012300000227000003)30.30a.busi_codei

5、n(131.31232.32433.33534.34835.351136.361437.371538.381739.391840.401941.412142.422487.|1|STGROUPBY||59|11741|1703(1)|||88.|2|FILTER|||||||89.|3|HASHJOINOUTER|||||||90.|4|TABLEACCESSBYLOCALINDEXROWID|RES_SIM|1|26|32(4)|||

6、91.|5|NESTEDLOOPS||46|7820|1670(1)|||92.|6|NESTEDLOOPS||49|7056|146(2)|||93.|7|NESTEDLOOPS||46|5244|53(2)|||94.|8|TABLEACCESSBYINDEXROWID|CM_BUSI_201108|46|4784|7(15)|||95.|9|INDEXRANGESCAN|DX_BUSI_SO_DATE_201108|166K||3

7、(34)|||96.|10|TABLEACCESSBYINDEXROWID|SYS_GANIZATIONS|1|10|2(50)|||97.|11|INDEXUNIQUESCAN|PK_SYS_GANIZATIONS|1|||||98.|12|TABLEACCESSBYGLOBALINDEXROWID|CM_USER|1|30|3(34)|ROWID|ROWL|99.|13|INDEXUNIQUESCAN|PK_ZK_CM_USER|1

8、||2(50)|||100.|14|PARTITIONRANGEALL|||||1|10|101.|15|INDEXRANGESCAN|IDX_SIM_SIM|1||31(4)|1|10|102.|16|TABLEACCESSFULL|CM_BUSI_GE_201108|474|13746|32(4)|||103.104.105.PredicateInfmation(identifiedbyoperationid):106.107.10

9、8.2filter(“B“.“BOOK_ITEM_ID“=23000001“B“.“BOOK_ITEM_ID“=23000002“B“.“BOOK_ITEM_ID“=27000109.“A“.“BUSI_CODE“=1“A“.“BUSI_CODE“=2“A“.“BUSI_CODE“=4“A“.“BUSI_CODE“=5“A“.“BUSI110.“A“.“BUSI_CODE“=11“A“.“BUSI_CODE“=14“A“.“BUSI_C

10、ODE“=15“A“.“BUSI_CODE“=17“A“.“111.“A“.“BUSI_CODE“=19“A“.“BUSI_CODE“=21“A“.“BUSI_CODE“=24“A“.“BUSI_CODE“=25“A“.“112.“A“.“BUSI_CODE“=99“A“.“BUSI_CODE“=101“A“.“BUSI_CODE“=104“A“.“BUSI_CODE“=105“A113.“A“.“BUSI_CODE“=204“A“.“

11、BUSI_CODE“=205“A“.“BUSI_CODE“=206“A“.“BUSI_CODE“=1006114.“A“.“BUSI_CODE“=2201“A“.“BUSI_CODE“=2251“A“.“BUSI_CODE“=3312)115.3access(“A“.“SO_NBR“=“B“.“SO_NBR“())116.8filter(“A“.“SO_COUNTY_CODE“=7111“A“.“OP_ID“=TO_DATE(20110