not in 和 not exists

Question

本帖最后由 chengccy2010 于 2011-12-03 11:04:19 编辑

Answer 1

not exists 是我用错了，现在一样可以执行的不过执行时间和not in 一样的。
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
这个SQL语句返回的结果比较少的不会超过100条
蓝色部分的SQL语句返回的结果有1000条左右。
想问下怎么提高这个SQL语句的速度，索引我也用了，并不明显

Answer 2

1、先回答你得第二个问题
你的t7.entertime 和 t6.entertime 上面肯定有索引。
你得第一种写法在 这个字段上加了一个 trunc 函数，导致索引失效了，而第二个没有导致索引失效，原因就是这么简单。2、再回答你的第一个问题，not in 和 not exists 不能等价的替换的，有一点点小区别，not exists 里面需要跟主表有关联关系的，根据你写的sql，我给你改一下。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);

Answer 3

补充一点：在T6和T7的 BUSSELFNO 字段上建一个索引。
另外：V_PI_POSINOUT_CCY 如果是个视图的话，建议先把数据查出来放到临时表中，建立索引再查询临时表的数据。

Answer 4

谢谢了。
让我知道了trunc函数会让索引失效。
第一个问题是我疏忽了，在2楼我说到了，不过执行速度和not in 没什么区别。我想问问怎么提高这个执行速度

Answer 5

in的效率要比exist效率高，这点是肯定的，其它请参考楼上回答。

Answer 6

另外补充一点，索引失效有很多因素，并不仅仅只有 trunc函数能导致，这一点你不要产生误会。

Answer 7

V_PI_POSINOUT_CCY 这个是个视图，是把几个表left join和union all 的结果。而且这的表的数据很多是时时更新的。
临时表不行的。用物化视图的话快速刷新不能有复杂查询语句的。这个sql慢就慢在 not in 这里，所以我想有没有更快的代替not in

Answer 8

我查了下not in 和not exists 的执行机制：
select * from a  where a.id not in (select b.id from b)
机制是：逐条扫描b表, 每扫描一条记录，去 a 表比对，存在返回一个布尔值，扫描下一条继续比对；
select * from a  where not exists(select b.id from b where b.id=a.id);
机制是：逐条扫描a表, 每扫描一条记录，去 b 表比对，存在返回一个布尔值，扫描下一条继续比对；
所以我用到minus来减少子查询的记录，加快速度。测试发现我的想法是对的。执行速度由原来的24秒减少到了4.7秒。在这里也谢谢大家的解答了。
修改后的代码：
select t1.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 in 
 (select t.busselfno
          from (select t1.busselfno
                  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
                minus
                select *
                  from (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'))) t);