select a.starttime, a.stoptime as endtime, a.msisdn as msid, a.url as destinationurl, b.dstip as destinationip,
b.dstport as destinationport, a.msip as clientip, null as clientport, 'cmwap' as apn
from t_dtl_wap_188188001117_012413 a left join
t_dtl_elog_188188001117_012413 b
on a.hostip = b.privtip
and a.localport = b.privtport
and a.spip= b.dstip
and a.spport= b.dstport
where to_date(a.starttime,'yyyymmddhh24miss')+5/86400 >= to_date(b.starttime,'yyyymmddhh24miss')
and a.stoptime <= b.stoptime
and a.starttime >= '20110124000000'
and a.starttime <= '20110125000000'where to_date(a.starttime,'yyyymmddhh24miss')+5/86400 >= to_date(b.starttime,'yyyymmddhh24miss')
and a.stoptime <= b.stoptime
这两个都用到全表扫描,效率太低,t_dtl_wap_188188001117_012413 ,t_dtl_elog_188188001117_012413 都建了索引
b.dstport as destinationport, a.msip as clientip, null as clientport, 'cmwap' as apn
from t_dtl_wap_188188001117_012413 a left join
t_dtl_elog_188188001117_012413 b
on a.hostip = b.privtip
and a.localport = b.privtport
and a.spip= b.dstip
and a.spport= b.dstport
where to_date(a.starttime,'yyyymmddhh24miss')+5/86400 >= to_date(b.starttime,'yyyymmddhh24miss')
and a.stoptime <= b.stoptime
and a.starttime >= '20110124000000'
and a.starttime <= '20110125000000'where to_date(a.starttime,'yyyymmddhh24miss')+5/86400 >= to_date(b.starttime,'yyyymmddhh24miss')
and a.stoptime <= b.stoptime
这两个都用到全表扫描,效率太低,t_dtl_wap_188188001117_012413 ,t_dtl_elog_188188001117_012413 都建了索引
to_date(a.starttime,'yyyymmddhh24miss')+a.starttime 是字符的而你则外面套用了to_date 函数 所以不会索引扫描建立函数索引 to_date(a.starttime,'yyyymmddhh24miss')
select a.starttime, a.stoptime as endtime, a.msisdn as msid, a.url as destinationurl, b.dstip as destinationip,
b.dstport as destinationport, a.msip as clientip, null as clientport, 'cmwap' as apn
from (select * from t_dtl_wap_188188001117_012413 union all select * from t_dtl_wap_188188001117_012414) a left join
(select * from t_dtl_elog_188188001117_012413 union all select * from t_dtl_elog_188188001117_012414) b
on a.hostip = b.privtip
and a.localport = b.privtport
and a.spip= b.dstip
and a.spport= b.dstport
where to_date(a.starttime,'yyyymmddhh24miss') >= to_date(b.starttime,'yyyymmddhh24miss') - 5/86400
and a.stoptime <= b.stoptime
and a.starttime >= '20110124000000'
and a.starttime <= '20110125000000';
ALTER SESSION SET nlc_date_format='yyyymmddhh24miss';
select a.starttime, a.stoptime as endtime, a.msisdn as msid, a.url as destinationurl, b.dstip as destinationip,
b.dstport as destinationport, a.msip as clientip, null as clientport, 'cmwap' as apn
from t_dtl_wap_188188001117_012413 a left join
t_dtl_elog_188188001117_012413 b
on a.hostip = b.privtip
and a.localport = b.privtport
and a.spip= b.dstip
and a.spport= b.dstport
WHERE a.starttime >= b.starttime-5/86400
and a.stoptime <= b.stoptime
and a.starttime >= to_date('20110124000000','yyyymmddhh24miss')
and a.starttime <= to_date('20110125000000','yyyymmddhh24miss')
ALTER SESSION SET nls_date_format='yyyymmddhh24miss';
a.starttime, a.stoptime as endtime, a.msisdn as msid, a.url as destinationurl, b.dstip as destinationip,
b.dstport as destinationport, a.msip as clientip, null as clientport, 'cmwap' as apn
from t_dtl_wap_188188001117_012413 a left join
t_dtl_elog_188188001117_012413 b
on a.hostip = b.privtip
and a.localport = b.privtport
and a.spip= b.dstip
and a.spport= b.dstport
where to_date(a.starttime,'yyyymmddhh24miss')+5/86400 >= to_date(b.starttime,'yyyymmddhh24miss')
and a.stoptime <= b.stoptime
and a.starttime like '2011012%'
a.starttime, a.stoptime as endtime, a.msisdn as msid, a.url as destinationurl, b.dstip as destinationip,
b.dstport as destinationport, a.msip as clientip, null as clientport, 'cmwap' as apn
from t_dtl_wap_188188001117_012413 a left join
t_dtl_elog_188188001117_012413 b
on a.hostip = b.privtip
and a.localport = b.privtport
and a.spip= b.dstip
and a.spport= b.dstport
where to_date(a.starttime,'yyyymmddhh24miss')+5/86400 >= to_date(b.starttime,'yyyymmddhh24miss')
and a.stoptime <= b.stoptime
and a.starttime like '201101223%'
a.starttime, a.stoptime as endtime, a.msisdn as msid, a.url as destinationurl, b.dstip as destinationip,
b.dstport as destinationport, a.msip as clientip, null as clientport, 'cmwap' as apn
from t_dtl_wap_188188001117_012413 a left join
t_dtl_elog_188188001117_012413 b
on a.hostip = b.privtip
and a.localport = b.privtport
and a.spip= b.dstip
and a.spport= b.dstport
where to_date(a.starttime,'yyyymmddhh24miss')+5/86400 >= to_date(b.starttime,'yyyymmddhh24miss')
and a.stoptime <= b.stoptime
and a.starttime >= '20110124000000'
and a.starttime <= '20110125000000'