NULL 值与索引

 NULL 值与索引

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值，通常称为UNKNOWN或空值，即是未知的，不确定的。由于
NULL存在着无数的可能，因此NULL值也不等于NULL值，所以与NULL值相关的操作同样都为NULL值。正是基于这样一个特性，对于NULL值列上的B
树索引导致了is null/is not null不走索引的情形，下面描述了NULL值与索引以及索引NULL列上的执行计划，如何使得NULL值走索引的情形。
注：本文仅仅讨论的是B树索引上的NULL值，位图索引不在此范围之内。

一、null值与索引的关系

[sql] view plain copy

 print?

  1. scott@ORCL> create table t1(id number,val varchar2(1));  
  2.   
  3. -->为表t1创建唯一索引  
  4. scott@ORCL> create unique index i_t1_id on t1(id);  
  5.   
  6. scott@ORCL> insert into t1 select null,'Y' from dual;  
  7.   
  8. scott@ORCL> insert into t1 select null,'N' from dual;  
  9.   
  10. -->从上面的操作可知，尽管列id上存在唯一索引，但由于null值不等于任一null值，因此能够成功插入  
  11. scott@ORCL> commit;  
  12.   
  13. -->再次为表添加唯一复合索引，即基于id列与val列  
  14. scott@ORCL> create unique index i_t1_id_val on t1(id,val);  
  15.   
  16. Index created.  
  17.   
  18. -->插入null,'N'的记录时失败，提示违反唯一性约束  
  19. scott@ORCL> insert into t1 select null,'N' from dual;  
  20. insert into t1 select null,'N' from dual  
  21. *  
  22. ERROR at line 1:  
  23. ORA-00001: unique constraint (SCOTT.I_T1_ID_VAL) violated  
  24.   
  25. -->插入null,'Y'的记录时同样失败，提示违反唯一性约束  
  26. scott@ORCL> insert into t1 select null,'Y' from dual;  
  27. insert into t1 select null,'Y' from dual  
  28. *  
  29. ERROR at line 1:  
  30. ORA-00001: unique constraint (SCOTT.I_T1_ID_VAL) violated  
  31.   
  32. -->插入两个null值成功  
  33. scott@ORCL> insert into t1 select null,null from dual;  
  34.   
  35. 1 row created.  
  36.   
  37. scott@ORCL> insert into t1 select null,null from dual;  
  38.   
  39. 1 row created.  
  40.   
  41. scott@ORCL> insert into t1 select null,'A' from dual;  
  42.   
  43. 1 row created.  
  44.   
  45. scott@ORCL> commit;  
  46.   
  47. Commit complete.  
  48.   
  49. scott@ORCL> set null unknown;  
  50. scott@ORCL> select * from t1;  
  51.   
  52.         ID VAL  
  53. ---------- ------------------------------  
  54. unknown    Y  
  55. unknown    N  
  56. unknown    unknown  
  57. unknown    unknown  
  58. unknown    A  
  59.   
  60. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);  
  61.            
  62. scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys  
  63.   2  from user_indexes  where table_name='T1';  
  64.   
  65. INDEX_NAME      INDEX_TYPE     BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS  
  66. --------------- ---------- ---------- ----------- ---------- -------- -------------  
  67. I_T1_ID         NORMAL              0           0          0 VALID                0  
  68. I_T1_ID_VAL     NORMAL              0           1          3 VALID                3  
  69.   
  70. -->从上面的情形可知，  
  71. -->基于单列的唯一索引，可以多次插入null值，但其索引上并不存储null值。  
  72. -->基于多列的复合索引，尽管全为null值的行可以多次插入，但不全为null的重复行则不能被插入(注,非唯一复合索引不存在此限制，此处不演示)。  
  73. -->基于多列的复合索引，对于全为null值的索引值也不会被存储。如上面的情形，尽管插入了5条记录，复合索引中只存储了3条。  
  74. -->注：对于唯一性约束，null值不等于null值，同样(null,null)也不等同于(null,null)，所以上面的两次null能够被插入。  

二、null值与执行计划

[sql] view plain copy

 print?

  1. scott@ORCL> set autot trace exp;  
  2. scott@ORCL> select * from t1 where id is null;  
  3.   
  4. Execution Plan  
  5. ----------------------------------------------------------  
  6. Plan hash value: 3617692013  
  7.   
  8. --------------------------------------------------------------------------  
  9. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  10. --------------------------------------------------------------------------  
  11. |   0 | SELECT STATEMENT  |      |     5 |     5 |     3   (0)| 00:00:01 |  
  12. |*  1 |  TABLE ACCESS FULL| T1   |     5 |     5 |     3   (0)| 00:00:01 |  
  13. --------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    1 - filter("ID" IS NULL)  
  19.   
  20. -->从上面的测试可知，由于null值是不被存储的，因此当使用id is null作为谓词时，走了全表扫描  
  21.      
  22. scott@ORCL> select * from t1 where id is not null;  
  23.   
  24. Execution Plan  
  25. ----------------------------------------------------------  
  26. Plan hash value: 796913935  
  27.   
  28. ---------------------------------------------------------------------------------------  
  29. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  30. ---------------------------------------------------------------------------------------  
  31. |   0 | SELECT STATEMENT            |         |     1 |     1 |     0   (0)| 00:00:01 |  
  32. |   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |     1 |     0   (0)| 00:00:01 |  
  33. |*  2 |   INDEX FULL SCAN           | I_T1_ID |     1 |       |     0   (0)| 00:00:01 |  
  34. ---------------------------------------------------------------------------------------  
  35.   
  36. Predicate Information (identified by operation id):  
  37. ---------------------------------------------------  
  38.   
  39.    2 - filter("ID" IS NOT NULL)  
  40.   
  41. -->从上面的测试可知，尽管当前表上id列上的所有值都为null，但不排除后续记录插入的id不为null的列。  
  42. -->故当使用id is not null作为谓词时，此时执行计划中走了索引全扫描。     
  43.   
  44. -->下面来看看复合索引的情形     
  45. scott@ORCL> select * from t1 where val is null;  
  46.   
  47. Execution Plan  
  48. ----------------------------------------------------------  
  49. Plan hash value: 3617692013  
  50.   
  51. --------------------------------------------------------------------------  
  52. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  53. --------------------------------------------------------------------------  
  54. |   0 | SELECT STATEMENT  |      |     2 |     2 |     3   (0)| 00:00:01 |  
  55. |*  1 |  TABLE ACCESS FULL| T1   |     2 |     2 |     3   (0)| 00:00:01 |  
  56. --------------------------------------------------------------------------  
  57.   
  58. Predicate Information (identified by operation id):  
  59. ---------------------------------------------------  
  60.   
  61.    1 - filter("VAL" IS NULL)  
  62.   
  63. scott@ORCL> select * from t1 where val is not null;  
  64.   
  65. Execution Plan  
  66. ----------------------------------------------------------  
  67. Plan hash value: 1931510411  
  68.   
  69. --------------------------------------------------------------------------------  
  70. | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  71. --------------------------------------------------------------------------------  
  72. |   0 | SELECT STATEMENT |             |     3 |     3 |     1   (0)| 00:00:01 |  
  73. |*  1 |  INDEX FULL SCAN | I_T1_ID_VAL |     3 |     3 |     1   (0)| 00:00:01 |  
  74. --------------------------------------------------------------------------------  
  75.   
  76. Predicate Information (identified by operation id):  
  77. ---------------------------------------------------  
  78.   
  79.    1 - filter("VAL" IS NOT NULL)  
  80.   
  81. -->对于复合唯一索引的情形，当使用单列且非前导列谓词时，使用is null与 is not null等同于单列唯一索引的情形。  
  82. -->即原理也是一样的，val is null走全表扫描而val is not null走索引。因为null值不会被存储。  
  83.   
  84. -->下面看看两个列都作为谓词的情形     
  85. scott@ORCL> select * from t1 where id is null and val is not null;  
  86.   
  87. Execution Plan  
  88. ----------------------------------------------------------  
  89. Plan hash value: 1040510552  
  90.   
  91. --------------------------------------------------------------------------------  
  92. | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  93. --------------------------------------------------------------------------------  
  94. |   0 | SELECT STATEMENT |             |     3 |     3 |     1   (0)| 00:00:01 |  
  95. |*  1 |  INDEX RANGE SCAN| I_T1_ID_VAL |     3 |     3 |     1   (0)| 00:00:01 |  
  96. --------------------------------------------------------------------------------  
  97.   
  98. Predicate Information (identified by operation id):  
  99. ---------------------------------------------------  
  100.   
  101.    1 - access("ID" IS NULL)  
  102.        filter("VAL" IS NOT NULL)  
  103.   
  104. -->从上面的测试可知，尽管两个谓词列上都存在索引，一个为单列唯一索引，一个为复合唯一索引。Oracle 选择了复合索引I_T1_ID_VAL。      
  105.   
  106. scott@ORCL> select * from t1 where id is not null and val is null;  
  107.   
  108. Execution Plan  
  109. ----------------------------------------------------------  
  110. Plan hash value: 796913935  
  111.   
  112. ---------------------------------------------------------------------------------------  
  113. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  114. ---------------------------------------------------------------------------------------  
  115. |   0 | SELECT STATEMENT            |         |     1 |     1 |     0   (0)| 00:00:01 |  
  116. |*  1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |     1 |     0   (0)| 00:00:01 |  
  117. |*  2 |   INDEX FULL SCAN           | I_T1_ID |     1 |       |     0   (0)| 00:00:01 |  
  118. ---------------------------------------------------------------------------------------  
  119.   
  120. Predicate Information (identified by operation id):  
  121. ---------------------------------------------------  
  122.   
  123.    1 - filter("VAL" IS NULL)  
  124.    2 - filter("ID" IS NOT NULL)      
  125.   
  126. -->同样的情形，谓词的顺序与复合索引定义的顺序一样，只不过第一个谓词为id is not null，而第二个谓词为val is null。  
  127. -->此时Oracle 选择了单列唯一索引I_T1_ID  
  128. -->看到此，不知道大家是否已明白，即哪个列为is not null，则会使用该列上的索引，原因还是那句话，索引不存储null值。  
  129. -->对于颠倒id列与val列以及id,val列为null或not null的其他不同组合情形不再演示，其执行计划类似。  

三、使用is null走索引的情形

[sql] view plain copy

 print?

  1. scott@ORCL> set autot off;  
  2. --删除原有表上的null值记录  
  3. scott@ORCL> delete from t1 where val not in('Y','N') or val is null;  
  4.   
  5. 3 rows deleted.  
  6.   
  7. scott@ORCL> update t1 set id=1 where val='Y';  
  8.   
  9. 1 row updated.  
  10.   
  11. scott@ORCL> update t1 set id=2 where val='N';  
  12.   
  13. 1 row updated.  
  14.   
  15. scott@ORCL> commit;  
  16.   
  17. Commit complete.  
  18.   
  19. -->对原有记录更新后的情形  
  20. scott@ORCL> select * from t1;  
  21.   
  22.         ID VAL  
  23. ---------- ------------------------------  
  24.          1 Y  
  25.          2 N  
  26.   
  27. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);  
  28.   
  29. PL/SQL procedure successfully completed.  
  30.   
  31. -->修改表列id使之具有not null约束的特性  
  32. scott@ORCL> alter table t1 modify(id not null);  
  33.   
  34. Table altered.  
  35.   
  36. scott@ORCL> set autot trace exp;  
  37. scott@ORCL> select * from t1 where id is null;  
  38.   
  39. Execution Plan  
  40. ----------------------------------------------------------  
  41. Plan hash value: 3160894736  
  42.   
  43. --------------------------------------------------------------------------------  
  44. | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  45. --------------------------------------------------------------------------------  
  46. |   0 | SELECT STATEMENT |             |     1 |     5 |     0   (0)|          |  
  47. |*  1 |  FILTER          |             |       |       |            |          |  
  48. |   2 |   INDEX FULL SCAN| I_T1_ID_VAL |     2 |    10 |     1   (0)| 00:00:01 |  
  49. --------------------------------------------------------------------------------  
  50.   
  51. Predicate Information (identified by operation id):  
  52. ---------------------------------------------------  
  53.   
  54.    1 - filter(NULL IS NOT NULL)  
  55.   
  56. -->从上面的执行计划中可知，当表t1列id上具有not null 约束时，此时使用id is null选择了索引范围扫描  
  57.   
  58. -->下面来看看列val is null 的情形     
  59. scott@ORCL> select * from t1 where val is null;  
  60.   
  61. Execution Plan  
  62. ----------------------------------------------------------  
  63. Plan hash value: 48744011  
  64.   
  65. ------------------------------------------------------------------------------------  
  66. | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  67. ------------------------------------------------------------------------------------  
  68. |   0 | SELECT STATEMENT     |             |     1 |     5 |     2   (0)| 00:00:01 |  
  69. |*  1 |  INDEX FAST FULL SCAN| I_T1_ID_VAL |     1 |     5 |     2   (0)| 00:00:01 |  
  70. ------------------------------------------------------------------------------------  
  71.   
  72. Predicate Information (identified by operation id):  
  73. ---------------------------------------------------  
  74.   
  75.    1 - filter("VAL" IS NULL)  
  76.   
  77. -->尽管val列上允许null值存在，但由于列id上具有not null 约束，且id列与val列存在复合唯一索引，因此此时选择了索引快速全扫描  
  78. -->其余不同组合情形大致相同，不再演示  
  79.   
  80. -->为表t1新增一条val为null的记录  
  81. scott@ORCL> insert into t1 select 3,null from dual;  
  82.   
  83. 1 row created.  
  84.   
  85. scott@ORCL> commit;  
  86.   
  87. Commit complete.  
  88.   
  89. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);  
  90.   
  91. PL/SQL procedure successfully completed.  
  92.   
  93. -->下面的查询中可以看出尽管只有列id有not null约束，当所有的索引值都被存储  
  94. scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys  
  95.   2  from user_indexes  where table_name='T1';  
  96.   
  97. INDEX_NAME      INDEX_TYPE     BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS  
  98. --------------- ---------- ---------- ----------- ---------- -------- -------------  
  99. I_T1_ID         NORMAL              0           1          3 VALID                3  
  100. I_T1_ID_VAL     NORMAL              0           1          3 VALID                3  
  101.   
  102. -->Author : Robinson Cheng  
  103. -->Blog :   http://blog.csdn.net/robinson_0612  

四、总结
    无论是单列唯一索引或复合唯一索引，对于可以为null的列或复合null值，Oracle不会为其存储索引值。
    故在基于单列创建B树唯一索引或多列创建B树复合唯一索引的情形下，
    当列上允许为null值时
        where子句使用了基于is null的情形，其执行计划走全表扫描。
        where子句使用了基于is not null的情形，其执行计划走索引扫描(索引范围扫描或索引全扫描)。
    当列上不允许为null值时，存在非null约束
        where子句使用了基于is null的情行，其执行计划走索引扫描。
        where子句使用了基于is not null的情形，其执行计划也是走索引扫描。
    注：此在Oracle 10g R2(linux)下的情形，不同的优化器版本可能会有偏差。

    在NULL值与索引(一)中讲述了null值与索引的一些基本情况。其主要的内容为，基于允许存在null值的索引列，其索引值不会被存储；其次
是由于这个特性导致了我们在使用is null时索引失效的情形；最后则是描述的通过为null值列添加not null约束来使得is null走索引。尽管我
们可以通过添加not null来解决is null走索引，当现实中的情况是仍然很多列根本是无法确定的，而必须保持其null特性。对于此种情形该如
何解决呢？

一、通过基于函数的索引来使得is null使用索引

[sql] view plain copy

 print?

  1. -->演示环境  
  2. scott@ORCL> select * from v$version where rownum<2;  
  3.   
  4. BANNER  
  5. ----------------------------------------------------------------  
  6. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod  
  7.   
  8. -->创建测试表t2  
  9. scott@ORCL> create table t2(obj_id,obj_name) as select object_id,object_name from dba_objects;  
  10.   
  11. Table created.  
  12.   
  13. -->演示表t2上不存在not null约束  
  14. scott@ORCL> desc t2  
  15.  Name                          Null?    Type  
  16.  ----------------------------- -------- --------------------  
  17.  OBJ_ID                                 NUMBER  
  18.  OBJ_NAME                               VARCHAR2(128)  
  19.   
  20. -->为表t2创建一个普通的B树索引  
  21. scott@ORCL> create index i_t2_obj_id on t2(obj_id);  
  22.   
  23. Index created.  
  24.   
  25. -->将表t2列obj_id<=100的obj_id置空  
  26. -->注：在Oracle 10g中空字符串等同于null值  
  27. scott@ORCL> update t2 set obj_id='' where obj_id<=100;  
  28.   
  29. 99 rows updated.  
  30.   
  31. -->下面的查询亦表明在此时空字符串等同于null值  
  32. scott@ORCL> set null unknown  
  33. scott@ORCL> select * from t2 where obj_id is null and rownum<3;  
  34.   
  35.     OBJ_ID OBJ_NAME  
  36. ---------- ------------------------------  
  37. unknown    ICOL$  
  38. unknown    I_USER1  
  39.   
  40. -->收集统计信息  
  41. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true);  
  42.   
  43. PL/SQL procedure successfully completed.  
  44.   
  45. -->基于null值上使用not null会使用索引扫描，等同于前面 null值与索引(一) 中的描述  
  46. scott@ORCL> select count(*) from t2 where obj_id is not null;  
  47.   
  48. Execution Plan  
  49. ----------------------------------------------------------  
  50. Plan hash value: 3840858596  
  51.   
  52. -------------------------------------------------------------------------------------  
  53. | Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  54. -------------------------------------------------------------------------------------  
  55. |   0 | SELECT STATEMENT      |             |     1 |     5 |     7   (0)| 00:00:01 |  
  56. |   1 |  SORT AGGREGATE       |             |     1 |     5 |            |          |  
  57. |*  2 |   INDEX FAST FULL SCAN| I_T2_OBJ_ID | 11719 | 58595 |     7   (0)| 00:00:01 |  
  58. -------------------------------------------------------------------------------------  
  59.   
  60. Predicate Information (identified by operation id):  
  61. ---------------------------------------------------  
  62.   
  63.    2 - filter("OBJ_ID" IS NOT NULL)  
  64.   
  65. -->列obj_id is null走全表扫描  
  66. scott@ORCL> select count(*) from t2 where obj_id is null;  
  67.   
  68. Execution Plan  
  69. ----------------------------------------------------------  
  70. Plan hash value: 3321871023  
  71.   
  72. ---------------------------------------------------------------------------  
  73. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  74. ---------------------------------------------------------------------------  
  75. |   0 | SELECT STATEMENT   |      |     1 |     5 |    13   (0)| 00:00:01 |  
  76. |   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |  
  77. |*  2 |   TABLE ACCESS FULL| T2   |     1 |     5 |    13   (0)| 00:00:01 |  
  78. ---------------------------------------------------------------------------  
  79.   
  80. Predicate Information (identified by operation id):  
  81. ---------------------------------------------------  
  82.   
  83.    2 - filter("OBJ_ID" IS NULL)  
  84.   
  85. -->创建基于函数的索引来使得is null走索引  
  86. -->下面使用了nvl函数来创建函数索引，即当obj_id为null值时，存储-1     
  87. scott@ORCL> create index i_fn_t2_obj_id on t2(nvl(obj_id,-1));  
  88.   
  89. Index created.  
  90.   
  91. -->收集索引信息  
  92. scott@ORCL> exec dbms_stats.gather_index_stats('SCOTT','I_FN_T2_OBJ_ID');  
  93.   
  94. PL/SQL procedure successfully completed.  
  95.   
  96. -->可以看到下面的执行计划中刚刚创建的函数索引已经生效I_FN_T2_OBJ_ID  
  97. scott@ORCL> select count(*) from t2 where nvl(obj_id,-1) = -1;  
  98.   
  99. Execution Plan  
  100. ----------------------------------------------------------  
  101. Plan hash value: 3983750858  
  102.   
  103. ------------------------------------------------------------------------------------  
  104. | Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |  
  105. ------------------------------------------------------------------------------------  
  106. |   0 | SELECT STATEMENT  |                |     1 |     5 |     1   (0)| 00:00:01 |  
  107. |   1 |  SORT AGGREGATE   |                |     1 |     5 |            |          |  
  108. |*  2 |   INDEX RANGE SCAN| I_FN_T2_OBJ_ID |   100 |   500 |     1   (0)| 00:00:01 |  
  109. ------------------------------------------------------------------------------------  
  110.   
  111. Predicate Information (identified by operation id):  
  112. ---------------------------------------------------  
  113.   
  114.    2 - access(NVL("OBJ_ID",(-1))=(-1))  

二、使用伪列创建基于函数的索引来使得is null使用索引

[sql] view plain copy

 print?

  1. -->下面通过添加一个值为-1(可取任意值)的伪列来创建索引  
  2. scott@ORCL> create index i_new_t2_obj_id on t2(obj_id,-1);  
  3.   
  4. Index created.  
  5.   
  6. -->收集索引信息  
  7. scott@ORCL> exec dbms_stats.gather_index_stats('SCOTT','I_NEW_T2_OBJ_ID');  
  8.   
  9. PL/SQL procedure successfully completed.     
  10.   
  11. -->从下面的查询可以看出obj_id is null使用了刚刚创建的索引  
  12. scott@ORCL> select count(*) from t2 where obj_id is null;  
  13.   
  14. Execution Plan  
  15. ----------------------------------------------------------  
  16. Plan hash value: 801885198  
  17.   
  18. -------------------------------------------------------------------------------------  
  19. | Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  
  20. -------------------------------------------------------------------------------------  
  21. |   0 | SELECT STATEMENT  |                 |     1 |     5 |     2   (0)| 00:00:01 |  
  22. |   1 |  SORT AGGREGATE   |                 |     1 |     5 |            |          |  
  23. |*  2 |   INDEX RANGE SCAN| I_NEW_T2_OBJ_ID |    99 |   495 |     2   (0)| 00:00:01 |  
  24. -------------------------------------------------------------------------------------  
  25.   
  26. Predicate Information (identified by operation id):  
  27. ---------------------------------------------------  
  28.   
  29.    2 - access("OBJ_ID" IS NULL)  
  30.   
  31. -->查看刚刚创建的所有索引的相关统计信息     
  32. scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys  
  33.   2  from user_indexes where table_name='T2';  
  34.   
  35. INDEX_NAME      INDEX_TYPE                         BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS  
  36. --------------- ------------------------------ ---------- ----------- ---------- -------- -------------  
  37. I_FN_T2_OBJ_ID  FUNCTION-BASED NORMAL                   1          26      11719 VALID            11621  
  38. I_NEW_T2_OBJ_ID FUNCTION-BASED NORMAL                   1          32      11719 VALID            11621  
  39. I_T2_OBJ_ID     NORMAL                                  1          25      11620 VALID            11620  
  40.   
  41. -->从上面的结果可知：  
  42. -->普通的B索引(I_T2_OBJ_ID)使用的索引块最小，因为null值没有被存储，NUM_ROWS与DISTINCT_KEYS即是佐证  
  43. -->使用NVL函数创建的索引I_FN_T2_OBJ_ID中如实的反应了null值，即11620 + null值 = 11621  
  44. -->使用伪列创建的索引依然属于函数索引，其耗用的叶节点块数最多，因为多出了一个值(-1)来存储  
  45. -->尽管使用NVL创建的函数占用的磁盘空间小于使用伪列创建的索引，当在书写谓词时需要带上NVL函数，而伪列索引中谓词直接使用is null。  

三、NULL值与索引衍生特性

[sql] view plain copy

 print?

  1. -->由前面的种种事例再次说明NULL值不会被存储到索引中，因此基于这个特性可以使用decode函数来压缩索引列。  
  2. -->在实际应用的多数情形中，如表上有打印状态列is_printed通常为两种情形，已打印或未打印，假定1表示已打印，而0表示未打印。  
  3. -->通常情况下90%以上的单据都处于已打印状态，而仅有10%左右的处于未打印。而经常要使用的情形是查询未打印的单据并重新打印。  
  4. -->基于上述情况，可以使用位图索引来解决，但此处我们讨论的是B树索引，故不考虑该情形(或者说你使用了非企业版Oracle，不支持位图索引)  
  5. -->此处对于这类情形我们可以使用decode函数来解决这个问题  
  6.   
  7. -->更新表上的列，使之obj_id为1的行占绝大多数  
  8. scott@ORCL> update t2 set obj_id=1 where obj_id is not null;  
  9.   
  10. 11620 rows updated.  
  11.   
  12. -->更新表，使之obj_id为0的行占少部分  
  13. scott@ORCL> update t2 set obj_id = 0 where obj_id is null;  
  14.   
  15. 99 rows updated.  
  16.   
  17. scott@ORCL> commit;  
  18.   
  19. -->收集统计信息  
  20. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true);  
  21.   
  22. PL/SQL procedure successfully completed.  
  23.   
  24. -->表t2上obj_id列的最终分布  
  25. scott@ORCL> select obj_id,count(*) from t2 group by obj_id;  
  26.   
  27.     OBJ_ID   COUNT(*)  
  28. ---------- ----------  
  29.          1      11620  
  30.          0         99     
  31.   
  32. -->使用decode函数创建索引  
  33. -->注意此处decode的使用，当obj_id非0值时，其值被赋予为null值，由于该null值不会存储到索引，因此大部分obj_id列值为1的不会被索引  
  34. scott@ORCL> create index i_fn2_t2_obj_id on t2(decode(obj_id,0,0,null));  
  35.   
  36. Index created.  
  37.   
  38. -->收集索引上的统计信息  
  39. scott@ORCL> exec dbms_stats.gather_index_stats('SCOTT','I_FN2_T2_OBJ_ID');  
  40.   
  41. PL/SQL procedure successfully completed.  
  42.   
  43. -->查看新索引的执行计划  
  44. scott@ORCL> set autot trace exp;  
  45. scott@ORCL> select count(*) from t2 where decode(obj_id,0,0,null) = 0;  
  46.   
  47. Execution Plan  
  48. ----------------------------------------------------------  
  49. Plan hash value: 1461308992  
  50.   
  51. -------------------------------------------------------------------------------------  
  52. | Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  
  53. -------------------------------------------------------------------------------------  
  54. |   0 | SELECT STATEMENT  |                 |     1 |     3 |     1   (0)| 00:00:01 |  
  55. |   1 |  SORT AGGREGATE   |                 |     1 |     3 |            |          |  
  56. |*  2 |   INDEX RANGE SCAN| I_FN2_T2_OBJ_ID |    98 |   294 |     1   (0)| 00:00:01 |  
  57. -------------------------------------------------------------------------------------  
  58.   
  59. Predicate Information (identified by operation id):  
  60. ---------------------------------------------------  
  61.   
  62.    2 - access(DECODE("OBJ_ID",0,0,NULL)=0)  
  63.   
  64. -->当直接使用obj_id = 0来查询时使用的是普通的B树索引  
  65. scott@ORCL> select count(*) from t2 where obj_id = 0;  
  66.   
  67. Execution Plan  
  68. ----------------------------------------------------------  
  69. Plan hash value: 1804118247  
  70.   
  71. ---------------------------------------------------------------------------------  
  72. | Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  73. ---------------------------------------------------------------------------------  
  74. |   0 | SELECT STATEMENT  |             |     1 |     3 |     1   (0)| 00:00:01 |  
  75. |   1 |  SORT AGGREGATE   |             |     1 |     3 |            |          |  
  76. |*  2 |   INDEX RANGE SCAN| I_T2_OBJ_ID |    99 |   297 |     1   (0)| 00:00:01 |  
  77. ---------------------------------------------------------------------------------  
  78.   
  79. Predicate Information (identified by operation id):  
  80. ---------------------------------------------------  
  81.   
  82.    2 - access("OBJ_ID"=0)     
  83.   
  84. -->当使用obj_id = 1来查询时走全表扫描，因为obj_id = 1占据表90%以上，由CBO特性决定了走全表扫描     
  85. scott@ORCL> select * from t2 where obj_id = 1;  
  86.   
  87. Execution Plan  
  88. ----------------------------------------------------------  
  89. Plan hash value: 1513984157  
  90.   
  91. --------------------------------------------------------------------------  
  92. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  93. --------------------------------------------------------------------------  
  94. |   0 | SELECT STATEMENT  |      | 11620 |   249K|    14   (8)| 00:00:01 |  
  95. |*  1 |  TABLE ACCESS FULL| T2   | 11620 |   249K|    14   (8)| 00:00:01 |  
  96. --------------------------------------------------------------------------  
  97.   
  98. Predicate Information (identified by operation id):  
  99. ---------------------------------------------------  
  100.   
  101.    1 - filter("OBJ_ID"=1)  
  102.      
  103. -->表t2上所有索引的统计信息  
  104. scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys  
  105.   2  from user_indexes where table_name='T2';  
  106.     
  107. INDEX_NAME      INDEX_TYPE                         BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS  
  108. --------------- ------------------------------ ---------- ----------- ---------- -------- -------------  
  109. I_FN_T2_OBJ_ID  FUNCTION-BASED NORMAL                   1          40      11719 VALID                2  
  110. I_NEW_T2_OBJ_ID FUNCTION-BASED NORMAL                   1          52      11719 VALID                2  
  111. I_FN2_T2_OBJ_ID FUNCTION-BASED NORMAL                   0           1         99 VALID                1  
  112. I_T2_OBJ_ID     NORMAL                                  1          40      11719 VALID                2  
  113.   
  114. -->从上面的结果可知，索引I_FN2_T2_OBJ_ID仅仅存储了99跳记录，且DISTINCT_KEYS值为1个，因为所有非0值的全部被置NULL。  
  115. -->以上方法实现了索引压缩，避免了较大索引维护所需的开销，同时也提高了查询性能。  
  116. -->Author : Robinson Cheng  
  117. -->Blog :   http://blog.csdn.net/robinson_0612  

四、总结
    1、对于用于连接或经常被谓词使用到的列应尽可能避免NULL值属性，因为它容易导致索引失效。
    2、为需要使用NULL值的列添加缺省值(alter table tb modify(col default 'Y'))。
    3、如果NULL值不可避免也不能使用缺省值，应考虑为该常用列使用nvl函数创建索引，或使用伪列来创建索引以提高查询性能。
    4、对于复合索引应保证索引中至少有一列不为NULL值，还是因为全部列为NULL时不被索引存储，以保证使用is null是可以使用索引。
    5、对于复合索引应保证索引列应使用数据类型长度最小的列来添加not null约束应节省磁盘空间。

About Me


...............................................................................................................................

● 本文转载自http://blog.csdn.net/leshami/article/details/7438397，乐沙弥大师

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599     微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
时间： 2024-09-15 08:39:28

NULL 值与索引的相关文章

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的.由于NULL存在着无数的可能,因此NULL值也不等于NULL值,所以与NULL值相关的操作同样都为NULL值.正是基于这样一个特性,对于NULL值列上的B树索引导致了is null/is not null不走索引的情形,下面描述了NULL值与索引以及索引NULL列上的执行计划,如何使得NULL值走索引的情形.注:本文仅仅讨论的是B树索引上的N

NULL 值与索引(二)

    在NULL值与索引(一)中讲述了null值与索引的一些基本情况.其主要的内容为,基于允许存在null值的索引列,其索引值不会被存储:其次是由于这个特性导致了我们在使用is null时索引失效的情形:最后则是描述的通过为null值列添加not null约束来使得is null走索引.尽管我们可以通过添加not null来解决is null走索引,当现实中的情况是仍然很多列根本是无法确定的,而必须保持其null特性.对于此种情形该如何解决呢? 一.通过基于函数的索引来使得is null使用索

Oracle技术：索引与Null值对于Hints及执行计划的影响

由于B*Tree索引不存储Null值,所以在索引字段允许为空的情况下,某些Oracle查询不会使用索引. 很多时候,我们看似可以使用全索引扫描(Full Index Scan)的情况,可能Oracle就会因为Null值的存在而放弃索引. 在此情况下即使使用Hints,Oracle也不会使用索引,其根本原因就是因为Null值的存在. 我们看以下测试. 在username字段为Not Null时,Index Hints可以生效. 更多精彩内容:http://www.bianceng.cnhttp:/

PostgreSQL 数据库NULL值的默认排序行为与查询、索引定义规范 - nulls first\last, asc\desc

标签 PostgreSQL , NULLS FIRST , NULLS LAST , ASC , DESC , 默认行为 , sort 背景 在数据库中NULL值是指UNKNOWN的值,不存储任何值,在排序时,它排在有值的行前面还是后面通过语法来指定. 例如 -- 表示null排在有值行的前面 select * from tbl order by id nulls first; -- 表示null排在有值行的后面 select * from tbl order by id nulls last;

当SQL Server排序时遇上了NULL值

这是一个经常被问到的问题.尤其是客户之前使用的是Oracle,那么他在使用SQL Server的时候会有 一个疑问,就是在处理NULL值上面,SQL Server与Oracle的行为不一样 在Oracle中,NULL值会认为是一个无穷大的值,所以如果按照升序排列的话,则会被排在最后面 在SQL Server中则正好相反,NULL值会被认为是一个无穷小的值,所以如果按照升序排列的话,则会 被排在最前面 如 SELECT [ID] FROM [demo].[dbo].[Orders] Order B

MySQL中的NULL值相关问题

对于SQL的新手,NULL值的概念常常会造成混淆,他们常认为NULL是与空字符串''相同的事.情况并非 如此.例如,下述语句是完全不同的: mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES (''); 这两条语句均会将值插入phone(电话)列,但第1条语句插入的是NULL值,第2条语句插入的是空字符串 .第1种情况的含义可被解释为"电话号码未知&

MySQL数据库中与 NULL值有关的几个问题

  对于SQL的新手,NULL值的概念常常会造成混淆,他们常认为NULL是与空字符串''相同的事.情况并非如此.例如,下述语句是完全不同的: mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES (''); 这两条语句均会将值插入phone(电话)列,但第1条语句插入的是NULL值,第2条语句插入的是空字符串.第1种情况的含义可被解释为"电话号码未知&

Sql与oracle中null值

  1 null值的介绍 NULL 是数据库中特有的数据类型,当一条记录的某个列为 NULL ,则表示这个列的值是未知的.是不确定的.既然是未知的,就有无数种的可能性.因此, NULL 并不是一个确定的值. 这是 NULL 的由来.也是 NULL 的基础,所有和 NULL 相关的操作的结果都可以从 NULL 的概念推导出来. 2 oracle中的null值介绍 在不知道具体有什么数据的时候,即未知,可以用NULL, 称它为空,ORACLE中,含有空值的表列长度为零.允许任何一种数据类型的字段为空

PostgreSQL NULL值相对位置与QUERY优化 - nulls first\last, asc\desc

标签 PostgreSQL , NULLS FIRST , NULLS LAST , ASC , DESC , 默认行为 , sort 背景 在数据库中NULL值是指UNKNOWN的值,不存储任何值,在排序时,它排在有值的行前面还是后面通过语法来指定. 例如 -- 表示null排在有值行的前面 select * from tbl order by id nulls first; -- 表示null排在有值行的后面 select * from tbl order by id nulls last;