select * from employee_list_view
where ylcs_info_id=15484 
and personnel_id not in ( 
select personnel_id from personnel_work_reg
where ylcs_info_id=15484 
and to_char((case when to_char(sysdate,'hh24')>=0 and to_char(sysdate,'hh24')<=3 then sysdate else sysdate+1 end),'yyyy-mm-dd')||' 04'>to_char(day,'yyyy-mm-dd hh24')
and to_char((case when to_char(sysdate,'hh24')>=0 and to_char(sysdate,'hh24')<=3 then sysdate-1 else sysdate end),'yyyy-mm-dd')||' 04'<=to_char(day,'yyyy-mm-dd hh24')
);在not in里的语句执行效率极低,其实现的功能就是决断一个时间范围,这个时间范围是在今天凌晨4点到明天的凌晨4点前都算是范围内,不知道这块怎么优化?
另外,除了这句慢外,再秋外面的语句结合,整个SQL语句执行的效率更是慢的让人崩溃
单独执行select * from employee_list_view
where ylcs_info_id=15484 
大概1S左右吧这样,但是整体执行时间大概要40S左右,请大家指点优化的方法

解决方案 »

  1.   

    --表personnel_work_reg列day上建立索引,使用not exists代替not in
    SELECT *
      FROM employee_list_view a
     WHERE ylcs_info_id = 15484
       AND NOT EXISTS
     (SELECT 1
              FROM personnel_work_reg t
             WHERE t.ylcs_info_id = a.ylcs_info_id
               AND t.DAY < trunc(CASE WHEN to_char(SYSDATE, 'hh24') >= 0
                             AND to_char(SYSDATE, 'hh24') <= 3 THEN SYSDATE ELSE
                                 SYSDATE + 1 END) + 1 / 6
               AND t.DAY >= trunc(CASE WHEN to_char(SYSDATE, 'hh24') >= 0
                              AND to_char(SYSDATE, 'hh24') <= 3 THEN SYSDATE - 1 ELSE
                                  SYSDATE END) + 1 / 6)
      

  2.   

    一般情况下,不要在条件列上使用函数(比如这儿的day),即使列day上建立了普通索引,也用不上。
    不得已情况下,如果确要使用函数,可建立函数索引。
    如建立函数索引:
    create index idx_day_func on personnel_work_reg(to_char(day,'yyyy-mm-dd hh24'));
      

  3.   

    作为开发人员来讲,尽量避免使用IN操作符,
    尽量使用上索引,如果条件列上使用了函数,可以使用函数索引(oracle开发还是蛮幸福的,有函数索引)。
      

  4.   


    select *
      from employee_list_view k
     where ylcs_info_id = 15484
       and not exists
     (select 1
              from personnel_work_reg m
             where to_char((case when to_char(sysdate, 'hh24') >= 0 and
                            to_char(sysdate, 'hh24') <= 3 then sysdate else
                            sysdate + 1 end),
                           'yyyy-mm-dd') || ' 04' >
                   to_char(day, 'yyyy-mm-dd hh24')
               and to_char((case when to_char(sysdate, 'hh24') >= 0 and
                            to_char(sysdate, 'hh24') <= 3 then sysdate - 1 else
                            sysdate end),
                           'yyyy-mm-dd') || ' 04' <=
                   to_char(day, 'yyyy-mm-dd hh24')
               and k.personnel_id = m.personnel_id
               and k.ylcs_info_id = m.ylcs_info_id);
      

  5.   

    not in 和  not  exits 的效率,哪个高一点  ?
      

  6.   

    in会对结果排序,前表大,后表小的时候,适合用not in。
    前表小,后表大时,适合用not exists,后边的表可以走索引。
    也不一定,具体看执行计划了
      

  7.   

    我一般不用 not in, 用not exists