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

查看原表：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> select count(*) from t1;     COUNT(*) ----------    3229088   SQL> select count(*) from t1 where object_id is null;     COUNT(*) ----------         32   SQL>

 

创建单键索引：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

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多的逻辑读和逻辑读。

 

创建复合索引：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

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