，关于优化查询

求助，关于优化查询
select inouttime, personno, personname, departmentname,DEPARTMENTCODE, inoutflag
from
(select rownum rn, personno, personname, inouttime, departmentname,DEPARTMENTCODE, inoutflag
from SC_INOUT_VALID_VIEW
where inouttime >= to_date('2012-09-14','YYYY-MM-DD HH24:MI:SS')
and inouttime <= to_date('2012-09-15','YYYY-MM-DD HH24:MI:SS'))
where rn in (select MAX(rownum) from SC_INOUT_VALID_VIEW where inouttime >= to_date('2012-09-14','YYYY-MM-DD HH24:MI:SS')
and inouttime <= to_date('2012-09-15','YYYY-MM-DD HH24:MI:SS') group by personno) and inoutflag = '进' and substr(DEPARTMENTCODE,1,2) = '12';

这个查询的作用是查询出某个部门某一时间段内最后一个状态是“进”的所有人的列表.....

80条记录用上面这个句子查用时1.5s，400多万条记录的时候最快也要几分钟，求优化......

personno,inouttime,DEPARTMENTCODE已做联合索引

------解决方案--------------------

SQL code


select inouttime, personno, personname, departmentname,DEPARTMENTCODE, inoutflag
    from SC_INOUT_VALID_VIEW t1
        where t1.inouttime=(select max(inouttime) from SC_INOUT_VALID_VIEW t2 
            where t1.personno=t2.personno and trunc(t2.inouttime)=to_date('2012-09-14','yyyy-mm-dd')
                and substr(DEPARTMENTCODE,1,2) = '12')--如果每個部門persor唯一,此處可省
            and inoutflag='进' and substr(DEPARTMENTCODE,1,2) = '12';

------解决方案--------------------

SQL code


SELECT INOUTTIME,
       PERSONNO,
       PERSONNAME,
       DEPARTMENTNAME,
       DEPARTMENTCODE,
       INOUTFLAG
  FROM (SELECT PERSONNO,
               PERSONNAME,
               INOUTTIME,
               DEPARTMENTNAME,
               DEPARTMENTCODE,
               INOUTFLAG,
               ROW_NUMBER() OVER(PARTITION BY SUBSTR(DEPARTMENTCODE, 1, 2) ORDER BY INOUTTIME DESC) ROW_
          FROM SC_INOUT_VALID_VIEW
         WHERE INOUTTIME >= TO_DATE('2012-09-14', 'YYYY-MM-DD HH24:MI:SS')
           AND INOUTTIME <= TO_DATE('2012-09-15', 'YYYY-MM-DD HH24:MI:SS')
           AND INOUTFLAG = '进')
 WHERE ROW_ = 1;

相关推荐