因为索引是不包含null字段的，所以当查询使用 is null作为条件时一般是使用不到索引（这里指B+树索引）而只有使用全表扫描了。但如查询的表特别大，is null的条件为必须，且没有其他好的选择条件，那么我们还是可以通过其他方式来创建一个复合索引，来将null 值也包含到索引中，而间接的让该查询也来走索引的。
可以创建的索引有下面几种：
1）普通复合索引 （col1,col2） col1 为查询条件为 is null的列， col2为有非空约束的列；
这种方式当查询条件 有 col1 is null 和 col2时会更高效，但索引相对更大。
2）带常数索引 （col1,‘1’） col1为查询条件 is null的列， 1为常量，也可是其他值。
只针对 col1 is null 适用。
下面来做个简单测试
创建测试表 test111;
HR@orcl>desc test111;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
--数据大小为100000
HR@orcl>select count(*) from test111;
COUNT(*)
----------
100000
--object_id is null 为1000
HR@orcl>select count(*) from test111 where object_id is null;
COUNT(*)
----------
10
HR@orcl>exec dbms_stats.gather_table_stats('HR','TEST111');
PL/SQL procedure successfully completed.
--开启autotrace
HR@orcl>set timing on;
HR@orcl>set lines 200
HR@orcl>set autotrace traceonly;
HR@orcl>alter session set statistics_level=all;
Session altered.
Elapsed: 00:00:00.04
--没有创建索引的情况下全表扫描，如下
HR@orcl>select * from test111 where object_id is null;
1000 rows selected.
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 3757802073
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 90000 | 379 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST111 | 1000 | 90000 | 379 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1435 consistent gets
0 physical reads
0 redo size
86606 bytes sent via SQL*Net to client
1245 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
HR@orcl>
–分别创建如下复合索引：
1）IND_TEST111_1OBJID (‘1’, OBJECT_ID)
2) IND_TEST111_2OBJID (OBJECT_ID, ‘2’)
3) IND_TEST111_OBJIDOWNER (OBJECT_ID, OWNER)
4) IND_TEST111_OWNEROBJID (OWNER, OBJECT_ID)
查询各种执行计划开销如下,可以看到虽然不同索引之间性能虽有较大差别，但比起全表扫描都是大大提升了。
使用IND_TEST111_2OBJID（索引范围扫描）cost为12， 逻辑读为14 为最小；
使用IND_TEST111_OBJIDOWNER（索引范围扫描）cost为12， 逻辑读为16 ；
使用IND_TEST111_OWNEROBJID（索引跳跃扫描） cost为16， 逻辑读为21 ；
使用IND_TEST111_1OBJID（索引全扫描） cost为249， 逻辑读为248。
HR@orcl>select /*+ full(t)*/* from test111 t where object_id is null;
10 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3757802073
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 940 | 379 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST111 | 10 | 940 | 379 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1370 consistent gets
0 physical reads
0 redo size
1793 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
HR@orcl>select /*+ index(t,IND_TEST111_OBJIDOWNER)*/* from test111 t where object_id is null;
10 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2382521718
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 940 | 12 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST111 | 10 | 940 | 12 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST111_OBJIDOWNER | 10 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1793 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
HR@orcl>select /*+ index(t,IND_TEST111_2OBJID)*/* from test111 t where object_id is null;
10 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2112287855
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 940 | 12 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST111 | 10 | 940 | 12 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST111_2OBJID | 10 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1793 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
HR@orcl>select /*+ index(t,IND_TEST111_OWNEROBJID)*/* from test111 t where object_id is null;
10 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 532671917
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 940 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST111 | 10 | 940 | 16 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_TEST111_OWNEROBJID | 10 | | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
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
21 consistent gets
0 physical reads
0 redo size
1793 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
HR@orcl>select /*+ index(t,IND_TEST111_1OBJID)*/* from test111 t where object_id is null;
10 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 235548458
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 940 | 249 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST111 | 10 | 940 | 249 (1)| 00:00:03 |
|* 2 | INDEX FULL SCAN | IND_TEST111_1OBJID | 10 | | 239 (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
248 consistent gets
0 physical reads
0 redo size
1793 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
