Question

Oracle Sql语句优化，并且时间条件不一样,查询所需时间相差很大

(SELECT DISTINCT CELLID RF, 'SD' STATION, mlb_sn SN

FROM F3102565.sn_tracking

WHERE flag = 0

AND mlb_sn IS NOT NULL

AND INSTR (stationid, 'Stockholm') > 0

AND CELLID LIKE 'L053FARF01%'

AND mlb_sn IN

(SELECT DISTINCT mlb_sn

FROM F3102565.sn_tracking

WHERE INSTR (stationid, 'CellCal') > 0

AND flag = 0

AND CELLID LIKE 'L053FARF01%'

AND edit_date >=

TO_DATE ('2017-06-05 23:00:00',

'YYYY/MM/DD HH24:MI:SS')

AND edit_date <=

TO_DATE ('2017-06-06 19:00:00',

'YYYY/MM/DD HH24:MI:SS')

AND mlb_sn IS NOT NULL))

UNION

SELECT DISTINCT CELLID RF, 'Cellcal' STATION, mlb_sn

FROM F3102565.sn_tracking

WHERE INSTR (stationid, 'CellCal') > 0

AND CELLID LIKE 'L053FARF01%'

AND flag = 0

AND edit_date >=

TO_DATE ('2017-06-05 23:00:00',

'YYYY/MM/DD HH24:MI:SS')

AND edit_date <=

TO_DATE ('2017-06-06 19:00:00',

'YYYY/MM/DD HH24:MI:SS')

AND mlb_sn IS NOT NULL

remark:

1.运行代码时的时间：2017-06-06 17:00:00

2.edit_date>2017-06-05 20:00:00&&<2017-06-06 19:00:00用时20s，是否可以优化？

3.edit_date>2017-06-05 23:00:00&&<2017-06-06 19:00:00无限制执行，为什么时间间隔更短还更慢？

4.无时间筛选也大概20s,只要edit_date的下限时间大于2017-06-05 20:00:00就非常慢

还请大神们帮忙解答，在线等

Answer 1

用上between and 效率能提高很多
top 也行你试试吧

追问

我刚改了一下使用between and 好像查询时间相差不大，为什么呢，我最疑问的是为什么时间选择在超过2017-06-05 20:00:00就非常非常慢，我都不知道什么时候能查出来？