，关于优化查询_Oracle技术

求助，关于优化查询
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 codeSELECT 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;