|
F5BB74E1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A3"."SERV_ID"=3745498320 OR "A3"."SERV_ID"=3751978420 OR "A3"."SERV_ID"=3751978950 OR "A3"."SERV_ID"=3751990561 OR
"A3"."SERV_ID"=3751991941 OR "A3"."SERV_ID"=3751992281 OR "A3"."SERV_ID"=3751992431 OR "A3"."SERV_ID"=3751992831 OR
"A3"."SERV_ID"=3751994541 OR "A3"."SERV_ID"=3751994561 OR "A3"."SERV_ID"=3753622431 OR "A3"."SERV_ID"=3753633921 OR
"A3"."SERV_ID"=3753633981 OR "A3"."SERV_ID"=3753634021 OR "A3"."SERV_ID"=3753634041 OR "A3"."SERV_ID"=3753634111 OR
"A3"."SERV_ID"=3753634271 OR "A3"."SERV_ID"=3753644141 OR "A3"."SERV_ID"=3753645051 OR "A3"."SERV_ID"=3753645261 OR
"A3"."SERV_ID"=3753647021)
8 - access("STAT"='S0C' AND "STATUS_DATE">=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access("ORDER_ITEM_ID"="A4"."SUBS_ID")
11 - filter("A2"."ACTION_TYPE"='NEW' AND "A4"."ACTION_ID"<>14030 AND "A4"."ACTION_ID"<>14266 AND "A4"."ACTION_ID"<>7021 OR
"A4"."ACTION_ID"=6448 OR "A4"."ACTION_ID"=14099 OR "A4"."ACTION_ID"=14260)
12 - access("A4"."ACTION_ID"="A2"."ACTION_ID" AND "A2"."CITY_ID"='200')
13 - filter("A1"."MODI_DATE"(+)>=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 - access("ORDER_ITEM_ID"="A1"."SUBS_ID"(+))
16 - access("ORDER_ITEM_ID"="A3"."SUBS_ID")
认真观察了下qb name和outline，因为a表是视图，指向ORDER_ITEM_HIST。很显然a表的qb name就是ORDER_ITEM_HIST@SEL$2
c表是执行计划id=1的地方，对应的qb name是1 - SEL$F5BB74E1 / A3@SEL$1，因此可用下面的方法优化。
--优化后：
SELECT /*+leading(A3@SEL$1,ORDER_ITEM_HIST@SEL$2)*/
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@togzodsd a,
crm_gz.tb_ba_action_hist@togzodsd b,
crm_gz.tb_ba_serv_hist@togzodsd c,
crm_gz.tb_pm_action@togzodsd 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@togzodsd
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');
--执行计划和outline
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | Inst |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SEL |