关于分页查询和column is null能否走索引的分析补充

群里有朋友在谈到关于分页查询的问题，类似下面的sql想让其走索引
select * from (select * from ta order by object_id desc) where rownum<10;
这位朋友在排序列上建立了索引，但是执行计划并不走索引来避免排序，而是全表扫描然后排序后取了前几条数据，这个消耗成本是很高的，我们来看看如何让这类分页查询走索引（这里的索引我们都理解为b tree索引，而不是bitmap索引）

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production

SQL> create table ta as select * from dba_objects;

Table created.

SQL> create index ind_id_null on ta(object_id);

Index created.

SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TA');

PL/SQL procedure successfully completed.

SQL> select * from ta where object_id is null;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 824468716

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TA | 1 | 101 | 292 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID" IS NULL)

Statistics
----------------------------------------------------------
42 recursive calls
0 db block gets
1078 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0        rows processed

这里看出cbo是不会走object_id列上的索引来避免排序和全表扫描。
SQL> select * from (select * from ta order by object_id desc) where rownum<10;

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2218702745

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1863 | | 2025 (1)| 00:00:25 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 74906 | 14M| | 2025 (1)| 00:00:25 |
|* 3 | SORT ORDER BY STOPKEY| | 74906 | 7388K| 9M| 2025 (1)| 00:00:25 |
| 4 | TABLE ACCESS FULL | TA | 74906 | 7388K| | 293 (1)| 00:00:04 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)

Statistics
----------------------------------------------------------
164 recursive calls
0 db block gets
1101 consistent gets
0 physical reads
0 redo size
2306 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
9 rows processed

那么这里有什么问题导致cbo不去考虑索引了，其实b tree索引存储的key是不能全部为null的，由于object_id列上没有not null的约束，而cbo的执行计划不能影响sql的执行结果，索引这里cbo没办法去认为通过索引回表，然后count stopkey取前几条来完成查询

而如果我们添加not null约束，或者在内部的查询结果中添加一个object_id is not null约束的过滤条件，那么此时cbo就知道了能够通过现在有的b tree索引回表的方式来完成查询
SQL> select * from (select * from ta where object_id is not null order by object_id desc) where rownum<10;

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 679434780

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1863 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 9 | 1863 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TA | 74906 | 7388K| 3 (0)| 00:00:01 |
|* 4 | INDEX FULL SCAN DESCENDING| IND_ID_NULL | 9 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)
4 - filter("OBJECT_ID" IS NOT NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
2306 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

那么如果业务中有object_id等于null的值，那么这个查询可能会影响结果，而且oracle对于null值的排序正是认为null是最大值的。

那么这个分页查询如果没有not null约束或者过滤条件，就不能走索引了吗，其实不然，小鱼之前处理过下面的类似的case，是对单个的列进行is null的谓词过滤

SQL> create index ind_id_multi_null on ta(1,object_id);

Index created.

SQL> select /*+index(ta,ind_id_multi_null)*/* from ta where object_id is null;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 849692407

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 199 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TA | 1 | 101 | 199 (1)| 00:00:03 |
|* 2 | INDEX FULL SCAN | IND_ID_MULTI_NULL | 1 | | 199 (1)| 00:00:03 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID" IS NULL)
filter("OBJECT_ID" IS NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
198 consistent gets
197 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0        rows processed

这个上面走的全索引扫描然后回表的方式来过滤的object_id is null的，这个是因为把索引的前导列弄错了导致的，如果我们建立下面的索引，把过滤列放在索引的前导列上
SQL> create index ind_id_nulti_null_bak on ta(object_id,1);

Index created.

SQL> select * from ta where object_id is null;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2610853831

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TA | 1 | 101 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID_NULTI_NULL_BAK | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID" IS NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0        rows processed

这个已经可以走这个复合索引的索引范围扫描了，那么最开始那个分页查询同样可以走全索引扫描，这个扫描只会扫描rownum分页数目的key然后回表，这个绝对比大表的全表扫描然后排序的成本要低很多。
SQL> select * from (select * from ta order by object_id desc) where rownum<10;

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2361786208

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1863 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 9 | 1863 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TA | 74906 | 7388K| 3 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN DESCENDING| IND_ID_NULTI_NULL_BAK | 9 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
2306 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

至此最开始那个分页查询我们已经优化完毕了。

这里有两点需要注意的地方：
1对于object_id is null这类过滤条件并不是不能走索引范围扫描的，我们只需要建立该列为前导列的复合索引就有可能让cbo考虑该索引
2还有就是分页查询要利用索引完成索引全扫描rownum分页数据的key然后回表的方式，一定要考虑该列是否有not null的约束或者过滤条件，这个可能造成部分分页查询无法通过索引完成。

早在N年前，韩国人就开始使用唯一索引来分页了，而不是rownum。中国程序员写的分页基本都是子查询、排序、rownum来处理的，效率没法比