NULL 值與索引(二)

bfc99發表於2014-06-27
Null索引
 以下轉自：http://blog.csdn.net/leshami/article/details/7438397 作者：Leshami

    在NULL值與索引(一)中講述了null值與索引的一些基本情況。其主要的內容為，基於允許存在null值的索引列，其索引值不會被儲存；其次
是由於這個特性導致了我們在使用is null時索引失效的情形；最後則是描述的通過為null值列新增not null約束來使得is null走索引。儘管我
們可以通過新增not null來解決is null走索引，當現實中的情況是仍然很多列根本是無法確定的，而必須保持其null特性。對於此種情形該如
何解決呢？

一、通過基於函式的索引來使得is null使用索引
[sql] view plaincopyprint?
  1. --&gt演示環境  
  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. --&gt建立測試表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. --&gt演示表t2上不存在not null約束  
  14. scott@ORCL> desc t2  
  15.  Name                          Null?    Type  
  16.  ----------------------------- -------- --------------------  
  17.  OBJ_ID                                 NUMBER  
  18.  OBJ_NAME                               VARCHAR2(128)  
  19.   
  20. --&gt為表t2建立一個普通的B樹索引  
  21. scott@ORCL> create index i_t2_obj_id on t2(obj_id);  
  22.   
  23. Index created.  
  24.   
  25. --&gt將表t2列obj_id<=100的obj_id置空  
  26. --&gt注：在Oracle 10g中空字串等同於null值  
  27. scott@ORCL> update t2 set obj_id='' where obj_id<=100;  
  28.   
  29. 99 rows updated.  
  30.   
  31. --&gt下面的查詢亦表明在此時空字串等同於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. --&gt收集統計資訊  
  41. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true);  
  42.   
  43. PL/SQL procedure successfully completed.  
  44.   
  45. --&gt基於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. --&gt列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. --&gt建立基於函式的索引來使得is null走索引  
  86. --&gt下面使用了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. --&gt收集索引資訊  
  92. scott@ORCL> exec dbms_stats.gather_index_stats('SCOTT','I_FN_T2_OBJ_ID');  
  93.   
  94. PL/SQL procedure successfully completed.  
  95.   
  96. --&gt可以看到下面的執行計劃中剛剛建立的函式索引已經生效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 plaincopyprint?
  1. --&gt下面通過新增一個值為-1(可取任意值)的偽列來建立索引  
  2. scott@ORCL> create index i_new_t2_obj_id on t2(obj_id,-1);  
  3.   
  4. Index created.  
  5.   
  6. --&gt收集索引資訊  
  7. scott@ORCL> exec dbms_stats.gather_index_stats('SCOTT','I_NEW_T2_OBJ_ID');  
  8.   
  9. PL/SQL procedure successfully completed.     
  10.   
  11. --&gt從下面的查詢可以看出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. --&gt檢視剛剛建立的所有索引的相關統計資訊     
  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. --&gt從上面的結果可知：  
  42. --&gt普通的B索引(I_T2_OBJ_ID)使用的索引塊最小，因為null值沒有被儲存，NUM_ROWS與DISTINCT_KEYS即是佐證  
  43. --&gt使用NVL函式建立的索引I_FN_T2_OBJ_ID中如實的反應了null值，即11620 + null值 = 11621  
  44. --&gt使用偽列建立的索引依然屬於函式索引，其耗用的葉節點塊數最多，因為多出了一個值(-1)來儲存  
  45. --&gt儘管使用NVL建立的函式佔用的磁碟空間小於使用偽列建立的索引，當在書寫謂詞時需要帶上NVL函式，而偽列索引中謂詞直接使用is null。  
三、NULL值與索引衍生特性
[sql] view plaincopyprint?
  1. --&gt由前面的種種事例再次說明NULL值不會被儲存到索引中，因此基於這個特性可以使用decode函式來壓縮索引列。  
  2. --&gt在實際應用的多數情形中，如表上有列印狀態列is_printed通常為兩種情形，已列印或未列印，假定1表示已列印，而0表示未列印。  
  3. --&gt通常情況下90%以上的單據都處於已列印狀態，而僅有10%左右的處於未列印。而經常要使用的情形是查詢未列印的單據並重新列印。  
  4. --&gt基於上述情況，可以使用點陣圖索引來解決，但此處我們討論的是B樹索引，故不考慮該情形(或者說你使用了非企業版Oracle，不支援點陣圖索引)  
  5. --&gt此處對於這類情形我們可以使用decode函式來解決這個問題  
  6.   
  7. --&gt更新表上的列，使之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. --&gt更新表，使之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. --&gt收集統計資訊  
  20. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true);  
  21.   
  22. PL/SQL procedure successfully completed.  
  23.   
  24. --&gt表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. --&gt使用decode函式建立索引  
  33. --&gt注意此處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. --&gt收集索引上的統計資訊  
  39. scott@ORCL> exec dbms_stats.gather_index_stats('SCOTT','I_FN2_T2_OBJ_ID');  
  40.   
  41. PL/SQL procedure successfully completed.  
  42.   
  43. --&gt檢視新索引的執行計劃  
  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. --&gt當直接使用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. --&gt當使用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. --&gt表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. --&gt從上面的結果可知，索引I_FN2_T2_OBJ_ID僅僅儲存了99跳記錄，且DISTINCT_KEYS值為1個，因為所有非0值的全部被置NULL。  
  115. --&gt以上方法實現了索引壓縮，避免了較大索引維護所需的開銷，同時也提高了查詢效能。  
  116. --&gtAuthor : Robinson Cheng  
  117. --&gtBlog :   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約束應節省磁碟空間。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22207394/viewspace-1196365/，如需轉載，請註明出處，否則將追究法律責任。

相關文章