现在系统中有一条SQL执行速度有时候特别快,有时候特别慢,通过对原有语句和执行计划的分析,发现将“rownum between :v_5 and :v_6;”由变量变为常量之后,执行计划会优化很多,但是该SQL在调整后反而执行消耗的时间更长了,哪位专家能帮帮忙,帮小弟分析一下这个问题,并给点处理建议.先谢谢各位了!
调优后的SQL语句,以及执行计划 SQL> select articleid,rownum irow from editorlib where deleteflag=:v_1 and approvalstate=:v_2 AND (deptid =:v_3 or destdeptid = :v_4) and rownum between 1 and 20 ORDER BY lastmodify DESC;
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter(ROWNUM<=20) 2 - filter(ROWNUM>=1) 3 - filter("APPROVALSTATE"=TO_NUMBER(:V_2) AND "DELETEFLAG"=TO_NUMBER(:V_1) AND ("DESTDEPTID"=TO_NUMBER(:V_4) OR "DEPTID"=TO_NUMBER(:V_3)))
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 521970 consistent gets 0 physical reads 0 redo size 1086 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed
调优前的SQL语句与执行计划 SQL> select * from ( 2 select kk.*, rownum irow from ( 3 select articleid from editorlib where deleteflag=:v_1 and approvalstate=:v_2 and (deptid =:v_3 or destdeptid = :v_4 ) ORDER BY lastmodify DESC 4 ) kk) aa where irow between :v_5 and :v_6;