|
ION | 1 | | 0 (0)| 00:00:01 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | TB_BA_CHANNELSTAFF | 1 | 52 | 2 (0)| 00:00:01 | | |
|* 17 | INDEX UNIQUE SCAN | PK_CHANNELSTAFF_SUBS_ID | 1 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
--方法2：/*+cardinality(a,220647)*/
SELECT XSY_CODE,--发展销售员编码
SLY_CODE,--受理销售员编码
XSD_CODE,--销售点编码
DZS_CODE,--店中商编码
JYZT_CODE--销售员所属经营主体编码
FROM (select /*+cardinality(a,220647)*/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 | | 14 | 1624 | 679 (1)| 00:00:10 | | |
|* 1 | HASH JOIN OUTER | | 14 | 1624 | 679 (1)| 00:00:10 | | |
| 2 | NESTED LOOPS | | 14 | 896 | 667 (1)| 00:00:10 | | |
| 3 | NESTED LOOPS | | 98 | 4704 | 569 (1)| 00:00:08 | | |
|* 4 | HASH JOIN | | 98 | 3430 | 275 (2)| 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 | | 220K| 4309K| 88 (0)| 00:00:02 | 1 | 39 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID | ORDER_ITEM_HIST | 220K| 4309K| 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_ACTION | 1 | | 0 (0)| 00:00:01 | | |
| 16 | TABLE ACCESS BY INDEX ROWID | TB_BA_CHANNELSTAFF | 19 | 988 | 11 (0)| 00:00:01 | | |
|* 17 | INDEX RANGE SCAN | IDX_BA_CHANNELSTAFF_MODI_DATE | 19 | | 3 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------------
于是，将方法告诉开发人员，结果本地执行还是很慢，查看执行计划，因为dblink的关系，用上面的2种方法都没生效。
那怎么办呢，用了各种hint都没法让他走hash，于是想到了 |