1)d.status 没有索引? 2)将1个 not exists 拆成两个,union all 的结果又没有索引。还有不要 SELECT *,reel_no 就够了吧。
SELECT d.work_order ,d.side ,d.machine_name ,d.reel_no ,d.create_time FROM ate.xx_faliao_travel d LEFT JOIN smt.g_wo_msl e ON d.work_order = e.work_order AND d.machine_name = e.machine_name AND decode(d.side, 'TOP', 0, 1) = e.side WHERE ( e.status = 'SMTLOADING' OR e.status = 'CREATE' ) AND ( NOT EXISTS (SELECT 1 FROM smt.g_smt_travel WHERE wo_sequence=e.wo_sequence) OR NOT EXISTS (SELECT 1 FROM smt.g_smt_status WHERE wo_sequence=e.wo_sequence) ) AND d.create_time < sysdate - 1 / 144 AND d.status = 2
加不加条件的运行时间没说反吗? #3的倒数第二行cardinality比#2高2个数量级。
select d.work_order, d.side, d.machine_name, d.reel_no, d.create_time from ate.xx_faliao_travel d left join smt.g_wo_msl e on d.work_order = e.work_order and d.machine_name = e.machine_name and decode(d.side, 'TOP', 0, 1) = e.side where (e.status = 'SMTLOADING' OR e.status = 'CREATE') and not exists (select 1 from smt.g_smt_status a left join smt.g_wo_msl b on a.wo_sequence = b.wo_sequence where (b.status = 'SMTLOADING' or b.status = 'CREATE') and d.reel_no = a.reel_no ) and not exists (select 1 from smt.g_smt_travel a left join smt.g_wo_msl b on a.wo_sequence = b.wo_sequence where (b.status = 'SMTLOADING' or b.status = 'CREATE') and d.reel_no = a.reel_no ) and d.create_time < sysdate - 1 / 144 and d.status = 1現在這條SQL語句需要0.5S左右 但是d.status = 2 的時候就要20S為什麼啊 其中status=2的數據比1的要少啊
2)将1个 not exists 拆成两个,union all 的结果又没有索引。还有不要 SELECT *,reel_no 就够了吧。
,d.side
,d.machine_name
,d.reel_no
,d.create_time
FROM ate.xx_faliao_travel d
LEFT JOIN smt.g_wo_msl e ON d.work_order = e.work_order
AND d.machine_name = e.machine_name
AND decode(d.side, 'TOP', 0, 1) = e.side
WHERE ( e.status = 'SMTLOADING' OR e.status = 'CREATE' )
AND ( NOT EXISTS (SELECT 1 FROM smt.g_smt_travel WHERE wo_sequence=e.wo_sequence)
OR NOT EXISTS (SELECT 1 FROM smt.g_smt_status WHERE wo_sequence=e.wo_sequence) )
AND d.create_time < sysdate - 1 / 144
AND d.status = 2
#3的倒数第二行cardinality比#2高2个数量级。
from ate.xx_faliao_travel d
left join smt.g_wo_msl e on d.work_order = e.work_order
and d.machine_name = e.machine_name
and decode(d.side, 'TOP', 0, 1) = e.side
where (e.status = 'SMTLOADING' OR e.status = 'CREATE')
and not exists
(select 1
from smt.g_smt_status a
left join smt.g_wo_msl b on a.wo_sequence = b.wo_sequence
where (b.status = 'SMTLOADING' or b.status = 'CREATE')
and d.reel_no = a.reel_no
)
and not exists
(select 1
from smt.g_smt_travel a
left join smt.g_wo_msl b on a.wo_sequence = b.wo_sequence
where (b.status = 'SMTLOADING' or b.status = 'CREATE')
and d.reel_no = a.reel_no
)
and d.create_time < sysdate - 1 / 144
and d.status = 1現在這條SQL語句需要0.5S左右
但是d.status = 2 的時候就要20S為什麼啊
其中status=2的數據比1的要少啊
FALIAO_TRAVEL_INDEX 里面加上 status 试试。