使用复合索引代替单键索引，来避免单键有null值的情况

查看原表：

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