|
1 98209 100
5 rows selected.
其实，在olap中走3分钟是还能接受的，但是看了一下执行计划，觉得还能再进一步优化。很明显，这里的hint没有起作用
/*+parallel(a,10) parallel(b,10) parallel(c,10) parallel(d,10)*/，执行计划并不走并行。其实，走并行使错的。
因为下面3个表非常大，加起来有100G，走再多的并行也难在3分钟内扫描完。
OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb)
-------------------- ------------------------------ -------------------- ----------
CRM_GZ TB_BA_ACTION_HIST TABLE 29305
CRM_GZ TB_BA_SERV_HIST TABLE PARTITION 30768.25
CRM_GZ ORDER_ITEM_HIST TABLE PARTITION 58503.625
|* 8 | INDEX RANGE SCAN | IXH_ORDERITEM_STATDATE | 12 |
执行计划中第8?有疑问，cbo认为只返回12行。直接手工算下，果然有问题。
select count(*) from CRM_GZ.ORDER_ITEM_HIST where "STAT"='S0C' AND "STATUS_DATE">=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss');
--220647
实际上返回220647行。那么问题来了，这里明显要走hash才是最优化的。这里有好几种方法都有效。
--方法1：/*+leading(c)*/
SELECT /*+leading(c)*/XSY_CODE,--发展销售员编码
SLY_CODE,--受理销售员编码
XSD_CODE,--销售点编码
DZS_CODE,--店中商编码
JYZT_CODE--销售员所属经营主体编码
FROM (select a.subs_id,c.serv_id,b.action_id,d.action_type,a.stat,a.stat_date
from crm_gz.tb_ba_subscription_hist a,
crm_gz.tb_ba_action_hist b,
crm_gz.tb_ba_serv_hist c,
crm_gz.tb_pm_action d
where a.subs_id=b.subs_id and a.subs_id=c.subs_id and b.action_id=d.action_id
and d.city_id='200' and a.stat='S0C') a,
(select * from crm_gz.tb_ba_channelstaff
where modi_date>=to_date('20141123','yyyymmdd')) b
WHERE a.subs_id=b.subs_id(+)
and a.stat_date>=to_date('20141123','yyyymmdd')
and ((a.action_type='NEW' and a.action_id not in(14030,14266,7021))
or a.action_id in(14099,14260,6448))
AND SERV_ID in ('3751990561',
'3751991941',
'3751992281',
'3751992431',
'3751992831',
'3751994541',
'3751994561',
'3753633921',
'3753633981',
'3753634021',
'3753634041',
'3753634111',
'3753634271',
'3753622431',
'3753644141',
'3753645051',
'3753645261',
'3753647021',
'3745498320',
'3751978420',
'3751978950');
--执行计划：
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 232 | 325 (1)| 00:00:05 | | |
| 1 | NESTED LOOPS OUTER | | 2 | 232 | 325 (1)| 00:00:05 | | |
| 2 | NESTED LOOPS | | 2 | 128 | 322 (1)| 00:00:05 | | |
| 3 | NESTED LOOPS | | 12 | 576 | 310 (1)| 00:00:05 | | |
|* 4 | HASH JOIN | | 12 | 420 | 274 (1)| 00:00:04 | | |
| 5 | PARTITION RANGE ALL | | 98 | 1470 | 185 (1)| 00:00:03 | 1 | 39 |
| 6 | INLIST ITERATOR | | | | | | | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_BA_SERV_HIST | 98 | 1470 | 185 (1)| 00:00:03 | 1 | 39 |
|* 8 | INDEX RANGE SCAN | IX_BA_SERVSERVID_HIST | 98 | | 100 (1)| 00:00:02 | 1 | 39 |
| 9 | PARTITION RANGE ALL | | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID | ORDER_ITEM_HIST | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 |
|* 11 | INDEX RANGE SCAN | IXH_ORDERITEM_STATDATE | 12 | | 79 (0)| 00:00:02 | 1 | 39 |
| 12 | TABLE ACCESS BY INDEX ROWID | TB_BA_ACTION_HIST | 1 | 13 | 3 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | IX_BA_ACT_SUBSID_HIST | 1 | | 2 (0)| 00:00:01 | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | TB_PM_ACTION | 1 | 16 | 1 (0)| 00:00:01 | | |
|* 15 | INDEX UNIQUE SCAN | PK_PM_ACT |