1 recursive calls
0 db block gets
74808 consistent gets
74730 physical reads
904 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
因为B-Tree索引不会记录null所以只能使用全表扫描。产生了大量的consistent gets下面我们来建立含常数的复合索引，并查询执行：
SYS@ORCL>create index idx_oid2_test_objects on test_objects(object_id,1);
Index created.
SYS@ORCL>select count(*) from test_objects where object_id is null;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 1238205220
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
|
|* 2 | INDEX RANGE SCAN| IDX_OID2_TEST_OBJECTS | 1 | 5 | 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
3 consistent gets
2 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到此时拥有含场数的复合索引之后就能够使用该索引查找null，极大的提升了执行效率。consistent gets从74808降到了3.效果非常明显。