在分页查询时，oracle绑定变量的使用优化

var a number

var b number

var c number

exec :a:=0

exec :b:=10

exec :c:=0

set serveroutput off;

alter session set statistics_level=all;

SELECT * FROM (SELECT T1_.*, rownum ROWNUM_ FROM (

SELECT O.* FROM yyf.testa O

WHERE 1=1

AND O.CREATE_TIME >= to_date('20150101 12:00:00', 'yyyymmdd hh24:mi:ss')

AND O.CREATE_TIME < to_date('20150723 23:59:59', 'yyyymmdd hh24:mi:ss')

order by o.CREATE_TIME desc

) T1_ WHERE ROWNUM <= (:a +:b )) WHERE ROWNUM_ >= (:c +1);

select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));

对应的执行计划为：

Plan hash value: 2754511479PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |-----------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT         |             |      1 |        |     10 |00:00:20.07 |     684K|    310K||*  1 |  VIEW                    |             |      1 |   4133K|     10 |00:00:20.07 |     684K|    310K||*  2 |   COUNT STOPKEY          |             |      1 |        |     10 |00:00:20.07 |     684K|    310K||   3 |    VIEW                  |             |      1 |   4133K|     10 |00:00:20.07 |     684K|    310K||*  4 |     SORT ORDER BY STOPKEY|             |      1 |   4133K|     10 |00:00:20.07 |     684K|    310K||*  5 |      TABLE ACCESS FULL   | testa       |      1 |   4133K|   5351K|00:00:15.40 |     684K|    310K|-----------------------------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("ROWNUM_">=:C+1)   2 - filter(ROWNUM<=:A+:B)   4 - filter(ROWNUM<=:A+:B)   5 - filter(("O"."CREATE_TIME">=TIMESTAMP' 2015-01-01 12:00:00' AND "O"."CREATE_TIME"

从上面执行计划可以看出，虽然在create_time 是not null ,并且有索引，而且该索引的可选择度非常高。但是该sql依然走的全表扫描。

接着对sql进行如下调整，去掉绑定变量的运算，即把条件由"WHERE ROWNUM <= (:a +:b )) WHERE ROWNUM_ >= (:c +1)" 改为 "WHERE ROWNUM <= (:b )) WHERE ROWNUM_ >= (:c) " ; 其执行计划为：

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                       | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |---------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                |                            |      1 |        |     10 |00:00:00.03 |      49 |      4 ||*  1 |  VIEW                           |                            |      1 |     10 |     10 |00:00:00.03 |      49 |      4 ||*  2 |   COUNT STOPKEY                 |                            |      1 |        |     10 |00:00:00.03 |      49 |      4 ||   3 |    VIEW                         |                            |      1 |     12 |     10 |00:00:00.03 |      49 |      4 ||   4 |     TABLE ACCESS BY INDEX ROWID | testa                      |      1 |   4133K|     10 |00:00:00.03 |      49 |      4 ||*  5 |      INDEX RANGE SCAN DESCENDING| IDX_testa_CREATETIME       |      1 |     12 |     10 |00:00:00.03 |      36 |      2 |---------------------------------------------------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("ROWNUM_">=:C)   2 - filter(ROWNUM<=:B)   5 - access("O"."CREATE_TIME"
        
         =TIMESTAMP' 2015-01-01 12:00:00')29 rows selected.