SQL> explain plan for select a.so_region_code so_region_code,
2 a.so_county_code so_county_code,
3 a.so_org_id so_org_id,
4 d.org_type_id org_type_id,
5 a.op_id op_id,
6 nvl(c.brand, 0) brand,
7 e.res_code,
8 a.busi_code,
9 a.so_nbr,
10 decode(a.isnormal,
11 2,
12 -count(distinct a.so_nbr),
13 0,
14 count(distinct a.so_nbr),
15 0) so_amount,
16 sum(decode(b.book_item_id, 23000002, item_total, 0)) / 100 sim_fee,
17 sum(decode(b.book_item_id, 23000001, item_total, 0)) / 100 sim_fee_add,
18 sum(decode(b.book_item_id, 27000003, item_total, 0)) / 100 sim_fee_discount,
19 sum(decode(b.book_item_id, 21000013, 0, b.item_total)) / 100 total_fee
20 from zk.cm_busi_201108 a,
21 zk.cm_busi_charge_201108 b,
22 zk.cm_user c,
23 xg.sys_organizations d,
24 zy.res_sim e
25 where a.so_nbr = b.so_nbr(+)
26 and a.serv_id = c.serv_id
27 and c.sim_id = e.sim_id
28 and a.so_org_id = d.org_id
29 and (b.book_item_id in (23000001, 23000002, 27000003) or
30 a.busi_code in (1,
31 2,
32 4,
33 5,
34 8,
35 11,
36 14,
37 15,
38 17,
39 18,
40 19,
41 21,
42 24,
43 25,
44 28,
45 99,
46 101,
47 104,
48 105,
49 201,
50 204,
51 205,
52 206,
53 2201,
54 1023,
55 1006,
56 3312,
57 2251))
58 and a.op_id != 71010264
59 and a.so_date >= to_date('20110831000000', 'yyyymmddhh24miss')
60 and a.so_date <= to_date('20110831235959', 'yyyymmddhh24miss')
61 and a.so_county_code =7111
62 and a.so_nbr is not null
63 group by a.so_region_code,
64 a.so_county_code,
65 a.so_org_id,
66 d.org_type_id,
67 a.op_id,
68 c.brand,
69 e.res_code,
70 a.busi_code,
71 a.so_nbr,
72 a.isnormal;
已解释。
已用时间: 00: 00: 00.03
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59 | 11741 | 1703 (1)| | |
| 1 | SORT GROUP BY | | 59 | 11741 | 1703 (1)| | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN OUTER | | | | | | |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | RES_SIM | 1 | 26 | 32 (4)| | |
| 5 | NESTED LOOPS | | 46 | 7820 | 1670 (1)| | |
| 6 | NESTED LOOPS | | 49 | 7056 | 146 (2)| | |
| 7 | NESTED LOOPS | | 46 | 5244 | 53 (2)| | |
|* 8 | TABLE ACCESS BY INDEX ROWID | CM_BUSI_201108 | 46 | 4784 | 7 (15)| | |
|* 9 | INDEX RANGE SCAN | DX_BUSI_SO_DATE_201108 | 166K| | 3 (34)| | |
| 10 | TABLE ACCESS BY INDEX ROWID | SYS_ORGANIZATIONS | 1 | 10 | 2 (50)| | |
|* 11 | INDEX UNIQUE SCAN | PK_SYS_ORGANIZATIONS | 1 | | | | |
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID| CM_USER | 1 | 30 | 3 (34)| ROWID | ROW L |
|* 13 | INDEX UNIQUE SCAN | PK_ZK_CM_USER | 1 | | 2 (50)| | |
| 14 | PARTITION RANGE ALL | | | | | 1 | 10 |
|* 15 | INDEX RANGE SCAN | IDX_SIM_SIM | 1 | | 31 (4)| 1 | 10 |
| 16 | TABLE ACCESS FULL | CM_BUSI_CHARGE_201108 | 474 | 13746 | 32 (4)| | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."BOOK_ITEM_ID"=23000001 OR "B"."BOOK_ITEM_ID"=23000002 OR "B"."BOOK_ITEM_ID"=27000
"A"."BUSI_CODE"=1 OR "A"."BUSI_CODE"=2 OR "A"."BUSI_CODE"=4 OR "A"."BUSI_CODE"=5 OR "A"."BUSI
"A"."BUSI_CODE"=11 OR "A"."BUSI_CODE"=14 OR "A"."BUSI_CODE"=15 OR "A"."BUSI_CODE"=17 OR "A"."
"A"."BUSI_CODE"=19 OR "A"."BUSI_CODE"=21 OR "A"."BUSI_CODE"=24 OR "A"."BUSI_CODE"=25 OR "A"."
"A"."BUSI_CODE"=99 OR "A"."BUSI_CODE"=101 OR "A"."BUSI_CODE"=104 OR "A"."BUSI_CODE"=105 OR "A
"A"."BUSI_CODE"=204 OR "A"."BUSI_CODE"=205 OR "A"."BUSI_CODE"=206 OR "A"."BUSI_CODE"=1006 OR
"A"."BUSI_CODE"=2201 OR "A"."BUSI_CODE"=2251 OR "A"."BUSI_CODE"=3312)
3 - access("A"."SO_NBR"="B"."SO_NBR"(+))
8 - filter("A"."SO_COUNTY_CODE"=7111 AND "A"."OP_ID"<>71010264 AND "A"."SO_NBR" IS NOT NULL)
9 - access("A"."SO_DATE">=TO_DATE(' 2011-08-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."SO_D
2011-08-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
11 - access("A"."SO_ORG_ID"="D"."ORG_ID")
13 - access("A"."SERV_ID"="C"."SERV_ID")
15 - access("C"."SIM_ID"="E"."SIM_ID")
已选择40行。
已用时间: 00: 00: 00.20
CM_BUSI_201108 是大表，有3千多万的数据，CM_USER也是一个大表，有3千多万的数据 其他表都是小表
注意观察第9行，CBO认为它返回166k的数据，回表的时候又过滤有filter过滤，这个时候CBO认为它返回46行，先不管这46行 CBO计算是对是错，单单就是索引扫描返回166k到表
CM_BUSI_201108 去做166k次应该也很耗费时间。所以给出优化建议 对表CM_BUSI_201108进行分区，可以根据SO_DATE做range分区，另外SO_COUNTRY_CODE可以查看值多不多，如果不多可以做 range-list分区
他最终只做了range分区，并且让他创建了一个本地有前缀的组合索引(他最开始创建的是global索引，没有起到优化效果)
create index YI_XXX ON CM_BUSI_201108(SO_DATE,SO_COUNTRY_CODE) LOCAL
执行计划如下：
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 3264 | 635 (1)| | |
| 1 | TABLE ACCESS BY LOCAL INDEX ROWID | RES_SIM | 1 | 26 | 32 (4)| | |
| 2 | NESTED LOOPS | | 17 | 3264 | 635 (1)| | |
| 3 | NESTED LOOPS | | 18 | 2988 | 75 (2)| | |
|* 4 | FILTER | | | | | | |
| 5 | NESTED LOOPS OUTER | | | | | | |
| 6 | NESTED LOOPS | | 17 | 1870 | 24 (5)| | |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| CM_BUSI_201108 | 17 | 1700 | 7 (15)| 6 | 6 |
|* 8 | INDEX RANGE SCAN | YI_XXX | 61917 | | 3 (34)| 6 | 6 |
| 9 | TABLE ACCESS BY INDEX ROWID | SYS_ORGANIZATIONS | 1 | 10 | 2 (50)| | |
|* 10 | INDEX UNIQUE SCAN | PK_SYS_ORGANIZATIONS | 1 | | | | |
|* 11 | INDEX RANGE SCAN | PK_CM_BUSI_CHARGE_201108 | 1 | 26 | 2 (50)| | |
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | CM_USER | 1 | 30 | 3 (34)| ROWID | ROW L |
|* 13 | INDEX UNIQUE SCAN | PK_ZK_CM_USER | 1 | | 2 (50)| | |
| 14 | PARTITION RANGE ALL | | | | | 1 | 10 |
|* 15 | INDEX RANGE SCAN | IDX_SIM_SIM | 1 | | 31 (4)| 1 | 10 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."BOOK_ITEM_ID"=23000001 OR "B"."BOOK_ITEM_ID"=23000002 OR "B"."BOOK_ITEM_ID"=27000003)
7 - filter("A"."OP_ID"<>71010264)
8 - access("A"."SO_DATE">=TO_DATE(' 2011-08-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."SO_COUNTY_CODE"=7111 AND
"A"."SO_DATE"<=TO_DATE(' 2011-08-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
filter("A"."SO_COUNTY_CODE"=7111)
10 - access("A"."SO_ORG_ID"="D"."ORG_ID")
11 - access("A"."SO_NBR"="B"."SO_NBR"(+))
13 - access("A"."SERV_ID"="C"."SERV_ID")
15 - access("C"."SIM_ID"="E"."SIM_ID")
分区之后，需要过滤的数据量大大减少，这样嵌套循环执行的次数也大大减少，最终SQL能在4秒左右跑完，其实这个优化方案并不是最优的，由于不能连接到他的数据库，这个SQL的优化就暂时告一段落。分区对SQL的优化还是非常有帮助的。