select t4.busselfno,
t4.LINENAME,
t4.busclass,
t4.DRIVERACODE,
t4.DRIVERANAME,
t4.FIRSTTIME,
to_char('') as DRIVERBCODE,
to_char('') as DRIVERBNAME,
to_date('', 'YYYY-MM-DD HH24:MI:SS') as CHANGETIME
from PI_BUSINFO t1
left join v_pi_dispatchplan_ccy t4
on t1.BUSSELFNO = t4.busselfno
where t1.AREAOWNER = 0
and t1.busstatus <> 0
and sysdate > t4.FIRSTTIME
and t4.busselfno not in (select t6.busselfno
from v_pi_posinout_ccy t6
where trunc(t6.entertime) = trunc(sysdate)
union all
select t7.busid
from pi_inout t7
where trunc(t7.entertime) = trunc(sysdate))
not in 的效率太慢的
而上面的sql语句 not in 换成not exists 为什么执行不了报错 ORA-00920: invalid relational operator
t4.busselfno 的字段类型是varchar2(20),t6.busselfno 的字段类型是varchar2(20),t7.busid 字段类型是varchar2(10),
不过我将 union all 后面的comment了也执行不了。
还有个问题我将蓝色代码部分换成
(select t6.busselfno
from v_pi_posinout_ccy t6
where t6.entertime between
to_date(to_char(sysdate, 'yyyy-mm-dd') || ' ' || '00:00:00',
'yyyy-mm-dd hh24:mi:ss') and
to_date(to_char(sysdate, 'yyyy-mm-dd') || ' ' || '23:59:59',
'yyyy-mm-dd hh24:mi:ss')
union all
select t7.busid from pi_inout t7 where t7.entertime between
to_date(to_char(sysdate, 'yyyy-mm-dd') || ' ' || '00:00:00',
'yyyy-mm-dd hh24:mi:ss') and
to_date(to_char(sysdate, 'yyyy-mm-dd') || ' ' || '23:59:59',
'yyyy-mm-dd hh24:mi:ss'))执行的速度快了11秒，这是为什么
------解决方案--------------------
1、先回答你得第二个问题
你的t7.entertime 和 t6.entertime 上面肯定有索引。
你得第一种写法在 这个字段上加了一个 trunc 函数，导致索引失效了，而第二个没有导致索引失效，原因就是这么简单。
2、再回答你的第一个问题，not in 和 not exists 不能等价的替换的，有一点点小区别，not exists 里面需要跟主表有关联关系的，根据你写的sql，我给你改一下。
- SQL code
SELECT T4.BUSSELFNO, T4.LINENAME, T4.BUSCLASS, T4.DRIVERACODE, T4.DRIVERANAME, T4.FIRSTTIME, TO_CHAR('') AS DRIVERBCODE, TO_CHAR('') AS DRIVERBNAME, TO_DATE('', 'YYYY-MM-DD HH24:MI:SS') AS CHANGETIME FROM PI_BUSINFO T1 LEFT JOIN V_PI_DISPATCHPLAN_CCY T4 ON T1.BUSSELFNO = T4.BUSSELFNO WHERE T1.AREAOWNER = 0 AND T1.BUSSTATUS <> 0 AND SYSDATE > T4.FIRSTTIME --AND T4.BUSSELFNO NOT IN AND NOT EXISTS (SELECT T6.BUSSELFNO FROM V_PI_POSINOUT_CCY T6 WHERE T6.ENTERTIME BETWEEN TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd') || ' ' || '00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd') || ' ' || '23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND T6.BUSSELFNO = T4.BUSSELFNO UNION ALL SELECT T7.BUSID FROM PI_INOUT T7 WHERE T7.ENTERTIME BETWEEN TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd') || ' ' || '00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd') || ' ' || '23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND T7.BUSSELFNO = T4.BUSSELFNO);
------解决方案--------------------
补充一点：在T6和T7的 BUSSELFNO 字段上建一个索引。
另外：V_PI_POSINOUT_CCY 如果是个视图的话，建议先把数据查出来放到临时表中，建立索引再查询临时表的数据。