0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (GROUP BY)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'CBQ_LCH_USAGE0'
4 2 TABLE ACCESS (FULL) OF 'CBQ_QF_USAGE1'
Elapsed: 13:48:26.85
调整：
not in 改成not exists
create index idx_serviceorderid on cbq_qf_usage1(serviceorderid) nologging;
select 'XJ'||1||'180','停机保号用户数',count(distinct serviceid),1,'200509',a.groupid
from cbq_lch_usage0 a
where a.subsidiaryid=1 and a.subid<>'02' and a.subid<>'06' and a.status='7'
and not exists(select 1 from cbq_qf_usage1 b where status<>'3' and status<>'8' and a.serviceid=b.serviceorderid)
group by 'XJ'||1||'180','停机保号用户数',1,'200509',a.groupid;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (GROUP BY)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'CBQ_LCH_USAGE0'
4 2 TABLE ACCESS (BY INDEX) OF 'CBQ_QF_USAGE1'
5 4 INDEX (RANGE SCAN) OF 'IDX_SERVICEORDERID'
本文出自“风中一叶”