在分页查询时，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: 2754511479
PLAN_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"<TIMESTAMP'
2015-07-23 23:59:59'))
31 rows selected.
从上面执行计划可以看出，虽然在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-07-23 23:59:59' AND "O"."CREATE_TIME">=TIMESTAMP' 2015-01-01 12:00:00')
29 rows selected.