通过案例学调优之--Oracle中null使用索引

通过案例学调优之--Oracle中null使用索引

默认情况下，Oracle数据库，null在Index上是不被存储的，当在索引列以"is null"的方式访问时，无法使用索引；本案例，主要向大家演示如何在存在null的索引列上，使用"is null"访问索引。

案例分析：

1、建立表和普通索引

13:52:23 SCOTT@ prod >create table t2 (x int,y int);Table created.14:00:11 SCOTT@ prod >insert into t2 values (1,1);1 row created.Elapsed: 00:00:00.0414:00:21 SCOTT@ prod >insert into t2 values (1,null);1 row created.Elapsed: 00:00:00.0014:00:31 SCOTT@ prod >insert into t2 values (null,1);1 row created.Elapsed: 00:00:00.0014:00:37 SCOTT@ prod >insert into t2 values (null,null);1 row created.Elapsed: 00:00:00.0014:00:44 SCOTT@ prod >commit;Commit complete.Elapsed: 00:00:00.0414:06:41 SCOTT@ prod >select * from t2;         X          Y---------- ----------         1          1         1                    1                    14:36:12 SCOTT@ prod >create index t2_ind on t2(x);Index created.14:49:38 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';INDEX_NAME                     TABLE_NAME                       NUM_ROWS------------------------------ ------------------------------ ----------T2_IND                         T2                                      3  在索引中只有3行，在最后一行字段全为null值，没有被存储！14:36:27 SCOTT@ prod >exec dbms_stats.gather_index_stats(user,'T2_IND');PL/SQL procedure successfully completed.14:37:29 SCOTT@ prod >select * from t2 where x=1;         X          Y---------- ----------         1          1         1Execution Plan----------------------------------------------------------Plan hash value: 1173409066--------------------------------------------------------------------------------------| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |        |     2 |     8 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     2 |     8 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | T2_IND |     2 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("X"=1)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size        519  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processed          14:37:45 SCOTT@ prod >select * from t2 where x is not null;         X          Y---------- ----------         1          1         1Execution Plan----------------------------------------------------------Plan hash value: 463061910--------------------------------------------------------------------------------------| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |        |     2 |     8 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     2 |     8 |     2   (0)| 00:00:01 ||*  2 |   INDEX FULL SCAN           | T2_IND |     2 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("X" IS NOT NULL)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size        519  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processed          14:38:00 SCOTT@ prod >select * from t2 where x is null;         X          Y---------- ----------                    1Execution Plan----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     2 |     8 |     3   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T2   |     2 |     8 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("X" IS NULL)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          8  consistent gets          0  physical reads          0  redo size        508  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processed当x通过"is null"访问时，Oracle选择了"full table scan"方式。

2、通过建立常量复合索引

14:38:55 SCOTT@ prod >create index t2_ind on t2(x,0);Index created.14:49:38 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';INDEX_NAME                     TABLE_NAME                       NUM_ROWS------------------------------ ------------------------------ ----------T2_IND                         T2                                      4   索引块上存储了表中所用的行。14:39:50 SCOTT@ prod >select * from t2 where x is null;         X          Y---------- ----------                    1Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------Plan hash value: 1173409066--------------------------------------------------------------------------------------| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |        |     2 |     8 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     2 |     8 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | T2_IND |     2 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("X" IS NULL)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size        508  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processed          对于x通过"is null"访问时，也能通过索引访问了！

3、建立复合索引（其他列为null）

13:59:40 SCOTT@ prod >create index x_ind on t2(x,y);Index created.14:08:29 SCOTT@ prod >EXEC dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T2');PL/SQL procedure successfully completed.14:09:22 SCOTT@ prod >EXEC dbms_stats.gather_index_stats(ownname=>USER,indname=>'X_IND');PL/SQL procedure successfully completed.14:09:58 SCOTT@ prod >select index_name,num_rows from user_indexes where index_name='X_IND';INDEX_NAME                       NUM_ROWS------------------------------ ----------X_IND                                   314:10:50 SCOTT@ prod >select count(*) from t2;  COUNT(*)----------         4         14:11:28 SCOTT@ prod >set autotrace on14:12:33 SCOTT@ prod >select * from t2 where x=1;         X          Y---------- ----------         1          1         1Execution Plan----------------------------------------------------------Plan hash value: 3708139238--------------------------------------------------------------------------| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT |       |     2 |     8 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| X_IND |     2 |     8 |     1   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("X"=1)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          2  consistent gets          0  physical reads          0  redo size        512  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processed          14:12:47 SCOTT@ prod >select * from t2 where x is not null;         X          Y---------- ----------         1          1         1Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------Plan hash value: 3776680409--------------------------------------------------------------------------| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT |       |     2 |     8 |     1   (0)| 00:00:01 ||*  1 |  INDEX FULL SCAN | X_IND |     2 |     8 |     1   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("X" IS NOT NULL)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          2  consistent gets          0  physical reads          0  redo size        512  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processed          14:13:08 SCOTT@ prod >select * from t2 where x is null;         X          Y---------- ----------                    1Execution Plan----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     2 |     8 |     3   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T2   |     2 |     8 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("X" IS NULL)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          8  consistent gets          0  physical reads          0  redo size        508  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processed           如果，复合索引列其他列也为null，在查询使用'is null'条件时，仍然为"full table scan"。          14:13:52 SCOTT@ prod >select * from t2 where x=1 and y is null;         X          Y---------- ----------         1Execution Plan----------------------------------------------------------Plan hash value: 3708139238--------------------------------------------------------------------------| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT |       |     1 |     4 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| X_IND |     1 |     4 |     1   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("X"=1 AND "Y" IS NULL)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          2  consistent gets          0  physical reads          0  redo size        471  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed          14:16:16 SCOTT@ prod >select * from t2 where x is null and y=1;         X          Y---------- ----------                    1Execution Plan----------------------------------------------------------Plan hash value: 3708139238--------------------------------------------------------------------------| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT |       |     1 |     4 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| X_IND |     1 |     4 |     1   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("X" IS NULL AND "Y"=1)       filter("Y"=1)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          1  consistent gets          0  physical reads          0  redo size        471  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

4、建立复合索引（其他列为 not null）

15:13:38 SCOTT@ prod >desc t2; Name                                                              Null?    Type ----------------------------------------------------------------- -------- -------------------------------------------- X                                                                          NUMBER(38) Y                                                                          NUMBER(38) 15:13:43 SCOTT@ prod >alter table t2 modify (y NUMBER(38) not null);Table altered.15:14:01 SCOTT@ prod >desc t2; Name                                                              Null?    Type ----------------------------------------------------------------- -------- -------------------------------------------- X                                                                          NUMBER(38) Y                                                                 NOT NULL NUMBER(38) 15:12:54 SCOTT@ prod >insert into t2 values (1,1);1 row created.Elapsed: 00:00:00.0215:13:02 SCOTT@ prod >insert into t2 values (null,1);1 row created.Elapsed: 00:00:00.0015:13:12 SCOTT@ prod >insert into t2 values (null,2);1 row created.Elapsed: 00:00:00.0015:13:36 SCOTT@ prod >commit;Commit complete.15:15:00 SCOTT@ prod >create index t2_ind on t2 (x,y);Index created.15:15:29 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);PL/SQL procedure successfully completed.15:16:09 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';INDEX_NAME                     TABLE_NAME                       NUM_ROWS------------------------------ ------------------------------ ----------T2_IND                         T2                                      315:17:20 SCOTT@ prod >set autotrace trace15:17:26 SCOTT@ prod >SELECT * from t2 where x is nullElapsed: 00:00:00.00Execution Plan----------------------------------------------------------Plan hash value: 2876512201---------------------------------------------------------------------------| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT |        |     2 |    10 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| T2_IND |     2 |    10 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("X" IS NULL)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          2  consistent gets          0  physical reads          0  redo size        510  bytes sent via SQL*Net to client        415  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processed在复合索引中，如果其他列为not null，则在"is null"条件下，仍然可以使用索引访问。

结论：

对于普通的索引，null值不能进行索引的正确理解应该是，对于某一行，索引的所有列的值都是null值时，该行才不能被索引。