欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

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

程序员文章站 2022-04-27 20:10:49
...

群里有朋友在谈到关于分页查询的问题，类似下面的sql想让其走索引 select * from (select * from ta order by object_id desc) where rownum 这位朋友在排序列上建立了索引，但是执行计划并不走索引来避免排序，而是全表扫描然后排序后取了前几条数据，这个

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

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 3 - filter(ROWNUM

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

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 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

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

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的约束或者过滤条件，这个可能造成部分分页查询无法通过索引完成。

原文地址：关于分页查询和column is null能否走索引的分析补充, 感谢原作者分享。

相关标签： 关于 分页 查询 column null 能否 索引 分析

上一篇： python和julia学哪个

下一篇： win7蓝牙无法添加设备

推荐阅读