这段sql该怎么优化？

[eeff]

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 都建了索引

[Phoenix_99]

看一下执行计划

[Oraclefans_]

[Quote=引用楼主 botbaby 的回复:]
SQL code
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, '……
[/Quote]
如果你索引列上使用的函数，或者是进行了算法的运算，他将会使用你的索引无效。。如果要走你索引必须使用函数索引

[心中的彩虹]

[Quote=引用楼主 botbaby 的回复:]
SQL code
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, ……
[/Quote]

to_date(a.starttime,'yyyymmddhh24miss')+

a.starttime 是字符的

而你则外面套用了to_date 函数 所以不会索引扫描

建立函数索引 to_date(a.starttime,'yyyymmddhh24miss')

[minitoy]

为什么不用date类型呢?

[eeff]

关联的两张表是用 union all 拼装的结果集，所以加了所以也没效果啊。。在t_dtl_wap_188188001117_上我建了to_date(starttime),但是还是慢啊。。

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';

[Oraclefans_]

执行计划贴出来看下。。

[QKForex]

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')

[QKForex]

ALTER SESSION SET nls_date_format='yyyymmddhh24miss';

[yonghengdizhen]

select/*+use_index(a 

你在t_dtl_wap_188188001117_012413上建立的以starttime为前置列的索引名称

)*/ 
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'

[惜分飞]

[Quote=引用 7 楼 huojianfashe 的回复:]

SQL code

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 de……
[/Quote]

正解

[lhl0228]

看看执行计划，应该使用函数的问题

[yonghengdizhen]

select/*+use_index(a 你在t_dtl_wap_188188001117_012413上建立的以starttime为前置列的索引名称)*/ 
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%'

[yonghengdizhen]

select/*+use_index(a 

[color=#FF0000]你在t_dtl_wap_188188001117_012413上建立的以starttime为前置列的索引名称[/color

])*/ 
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%'