我建了一个表,里面有日期(RQ),时间(SJ)两个字段,都是DATE类型,我建立了一个查询,目的是找到两个日期之间所有的数据。我的SQL语句是这样写的:SELECT SID, SSJSID, RQ, SJ,
FROM JING_SS WHERE SID IN
(SELECT SID FROM JING_SS WHERE RQ IN( SELECT RQ FROM JING_SS WHERE TO_CHAR(RQ,'yyyyMMdd') BETWEEN 开始日期 AND 结束日期 ))
AND SID NOT IN (SELECT SID FROM JING_SS WHERE TO_CHAR(SJ,'HH24mmss') < 开始时间 AND TO_CHAR(RQ,'yyyyMMdd') = 开始日期 )
AND SID NOT IN (SELECT SID FROM JING_SS WHERE TO_CHAR(SJ,'HH24mmss') > 结束时间 AND TO_CHAR(RQ,'yyyyMMdd') = 结束日期)
AND SSJSID IN (SELECT SID FROM COM_DW CONNECT BY PRIOR SID = FID START WITH SID = 单位编号 )
ORDER BY SID;在数据量少的时候查询速度还是能够忍受的,可是现在有大约60万条数据,时间就太长了,我想优化这条查询,请教各路大神!拜谢!!!分不多,谢谢各位,如果可行马上给分。oracle优化sql
FROM JING_SS WHERE SID IN
(SELECT SID FROM JING_SS WHERE RQ IN( SELECT RQ FROM JING_SS WHERE TO_CHAR(RQ,'yyyyMMdd') BETWEEN 开始日期 AND 结束日期 ))
AND SID NOT IN (SELECT SID FROM JING_SS WHERE TO_CHAR(SJ,'HH24mmss') < 开始时间 AND TO_CHAR(RQ,'yyyyMMdd') = 开始日期 )
AND SID NOT IN (SELECT SID FROM JING_SS WHERE TO_CHAR(SJ,'HH24mmss') > 结束时间 AND TO_CHAR(RQ,'yyyyMMdd') = 结束日期)
AND SSJSID IN (SELECT SID FROM COM_DW CONNECT BY PRIOR SID = FID START WITH SID = 单位编号 )
ORDER BY SID;在数据量少的时候查询速度还是能够忍受的,可是现在有大约60万条数据,时间就太长了,我想优化这条查询,请教各路大神!拜谢!!!分不多,谢谢各位,如果可行马上给分。oracle优化sql
SELECT SID, SSJSID, RQ, SJ,
FROM JING_SS
WHERE TO_DATE(RQ || ' ' || SJ,'yyyy-mm-dd HH24:mi:ss')
BETWEEN TO_DATE(开始日期 || ' ' || 开始时间,'yyyy-mm-dd HH24:mi:ss')
and TO_DATE(结束日期 || ' ' || 结束时间,'yyyy-mm-dd HH24:mi:ss')
SELECT SID, SSJSID, RQ, SJ FROM JING_SS
Where to_char(rq,'yyyyMMdd') between 开始日期 and 结束日期 and
not (to_char(sj,’hh24mmss')<开始时间and to_char(rq,'yyyyMMdd')=开始日期) and
not (to_char(sj,'hh24mmss')>结束时间and to_char(rq,'yyyyMMdd')=结束日期) and
ssjsid in (select sid fromcom_dw connect by prior sid=fid start with sid=单位编号)
Order by sid;
不过还是非常感谢!给分了。