查看原表：
SQL> select count(*) from t1; COUNT(*)
----------
3229088 SQL> select count(*) from t1 where object_id is null; COUNT(*)
----------
32 SQL>
创建单键索引：
SQL> create index idx_t1 on t1(object_id);
SQL> alter system flush buffer_cache;
SQL> set timing on
SQL> set autot traceonly
SQL> select * from t1 where object_id is null; 32 rows selected. Elapsed: 00:00:00.21 Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013 --------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 499 | 78842 | 12320 (1)| 00:02:28 |
|* 1 | TABLE ACCESS FULL| T1 | 499 | 78842 | 12320 (1)| 00:02:28 |
-------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("OBJECT_ID" IS NULL) Note
-----
- dynamic sampling used for this statement (level=2) Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
45453 consistent gets
45585 physical reads
0 redo size
2195 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32 rows processed SQL>
对于普通的单键值B树索引而言，null值不入索引。所以即使在object_id上有单键值B树索引，在执行"select * from t1 where object_id is null"时也用不上索引。上述查询分别消耗了4w多的逻辑读和逻辑读。
创建复合索引：
SQL> drop index idx_t1;
SQL> create index idx_t1 on t1(object_id,0);
SQL> alter system flush buffer_cache;
SQL> select * from t1 where object_id is null; 32 rows selected. Elapsed: 00:00:00.00 Execution Plan
----------------------------------------------------------
Plan hash value: 50753647 --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 3136 | 35 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 32 | 3136 | 35 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 32 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("OBJECT_ID" IS NULL) Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
35 physical reads
0 redo size
2195 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32 rows processed SQL>
创建复合索引后，物理读和逻辑读变成40多。