Oracle中的NULL(六)
最近在論壇上經常看到,很多人提出和NULL有關的問題。NULL其實是資料庫中特有的型別,Oracle中很多容易出現的錯誤都是和NULL有關的。
打算簡單的總結一下NULL的相關知識。
這篇文件討論一下NULL和索引的關係。
Oracle中的NULL(一):http://yangtingkun.itpub.net/post/468/244434
Oracle中的NULL(二):http://yangtingkun.itpub.net/post/468/245107
Oracle中的NULL(三):http://yangtingkun.itpub.net/post/468/245259
Oracle中的NULL(四):http://yangtingkun.itpub.net/post/468/245697
Oracle中的NULL(五):http://yangtingkun.itpub.net/post/468/247492
前面幾篇文章討論了NULL的資料型別和NULL的運算特點。這裡打算簡單描述NULL和索引的關係。
如果說NULL型別已經比較容易出錯了,那麼索引問題就讓NULL又一次成為問題的焦點。
大多數人都聽說過這樣一句話,索引不儲存NULL值。這句話其實比不嚴謹。如果採用比較嚴謹的方式來說:B樹索引不儲存索引列全為空的記錄。如果把這句話用在單列索引上,就是前面提到的B樹索引不儲存NULL。
首先索引分為BTREE和BITMAP兩種,對於BTREE索引,是不儲存NULL值的,而對於BITMAP索引,是儲存NULL值的。
而從索引列的個數來劃分,索引非為單列索引和複合索引,對於單列索引來說很簡單,如果一條記錄中這個索引欄位為空,那麼索引不會儲存這條記錄的資訊。但是對於複合索引,由於存在著多個列,如果某一個索引列不為空,那麼索引就會包括這條記錄,即使其他所有的所有列都是NULL值。
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已建立。
SQL> DESC T
名稱 是否為空? 型別
-------------------------------------------- -------- ------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> CREATE INDEX IND_T_OBJECT_ID ON T (OBJECT_ID);
索引已建立。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', CASCADE => TRUE)
PL/SQL 過程已成功完成。
SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
50297
執行計劃
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50297 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------
SQL> SELECT /*+ INDEX(T IND_T_OBJECT_ID) */ COUNT(*) FROM T;
COUNT(*)
----------
50297
執行計劃
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50297 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------
Oracle的最佳化器在確定是否使用索引的時候,第一標準是能否得到一個正確的結果。由於OBJECT_ID是可以為空的,而索引列不包含為空的記錄。因此透過索引掃描無法得到一個正確的結果,這就是SELECT COUNT(*) FROM T不會使用OBJECT_ID上的索引的原因。
而對於BITMAP索引,則是另外的情況:
SQL> DROP INDEX IND_T_OBJECT_ID;
索引已刪除。
SQL> CREATE BITMAP INDEX IND_B_T_DATA_ID ON T (DATA_OBJECT_ID);
索引已建立。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
50297
執行計劃
----------------------------------------------------------
Plan hash value: 3051411170
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | BITMAP CONVERSION COUNT| | 50297 | 2 (0)|
| 3 | BITMAP INDEX FULL SCAN| IND_B_T_DATA_ID | | |
-------------------------------------------------------------------------
SQL> SELECT COUNT(*) FROM T WHERE DATA_OBJECT_ID IS NULL;
COUNT(*)
----------
46452
執行計劃
----------------------------------------------------------
Plan hash value: 2587852253
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 2| 2 (0)|
| 1 | SORT AGGREGATE | | 1| 2| |
| 2 | BITMAP CONVERSION COUNT | | 46452| 92904| 2 (0)|
|* 3 | BITMAP INDEX SINGLE VALUE| IND_B_T_DATA_ID| | | |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DATA_OBJECT_ID" IS NULL)
從上面的結果不難看出BITMAP索引中是包含NULL的。
下面看看複合索引的情況:
SQL> DROP INDEX IND_B_T_DATA_ID;
索引已刪除。
SQL> CREATE INDEX IND_T_OBJECT_DATA ON T(OBJECT_ID, DATA_OBJECT_ID);
索引已建立。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS')
PL/SQL 過程已成功完成。
SQL> SELECT OBJECT_ID, DATA_OBJECT_ID FROM T WHERE OBJECT_ID = 135;
OBJECT_ID DATA_OBJECT_ID
---------- --------------
135
執行計劃
----------------------------------------------------------
Plan hash value: 1726226519
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)|
|* 1 | INDEX RANGE SCAN| IND_T_OBJECT_DATA | 1 | 7 | 1 (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=135)
雖然結果中包含了NULL值,但是Oracle並沒有讀取表,而僅僅透過索引掃描就返回了結果,這說明覆合索引中是可能包含NULL值的。
本文簡單說明了索引和NULL值的關係。這裡並沒有對反鍵索引(reverse)、逆序索引(desc)、函式索引(FBI)和CLUSTER索引進行說明。
原因是這些索引其實都屬於離不開BTREE索引和BITMAP索引的範疇。不必關心索引是否倒序或反鍵,只要是BTREE索引,就不會儲存全NULL記錄,反之,只要是BITMAP索引就會儲存NULL值。
唯一需要注意的是函式索引,函式索引的真正索引列是函式的計算結果而不是行記錄中的資料,清楚了這一點函式索引其實和普通索引就沒有什麼區別了。
最後說明一下域索引。由於域索引的實現本身可能會很複雜,Oracle可能在內部是用一套表和過程來實現的,因此對於域索引是否儲存NULL,要根據域索引的實現去進行具體的分析了。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-69139/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle中的nullOracleNull
- Oracle中的NULL(五)OracleNull
- Oracle中的NULL(八)OracleNull
- oracle中關於null的定義OracleNull
- MySQL中is not null和!=null和<>null的區別MySqlNull
- Oracle唯一約束中NULL的處理OracleNull
- 【NULL】Oracle null值介紹NullOracle
- Oracle外來鍵約束中NULL的處理OracleNull
- 主題:Oracle中Null與空字串''''的區別OracleNull字串
- ORACLE -> NULL & INDEXESOracleNullIndex
- hive中的null值HiveNull
- 面試題((A)null).fun()——java中null值的強轉面試題NullJava
- 關於 oracle NULLOracleNull
- 大話Oracle nullOracleNull
- 警惕SQL中間的NULLSQLNull
- SQL中的空值NULLSQLNull
- SQL server中的NULL值SQLServerNull
- ORACLE關於NULL的總結OracleNull
- Oracle空串與null的處理OracleNull
- MySQL中IS NULL、IS NOT NULL、!=不能用索引?胡扯!MySqlNull索引
- SQL 查詢中的 NULL 值SQLNull
- 去除陣列中的 null 值陣列Null
- JavaScript中的“undefined、null”區別?JavaScriptUndefinedNull
- 深入詳解SQL中的NullSQLNull
- sql中的安全問題nullSQLNull
- Oracle 11g中的_optimizer_null_aware_antijoin隱含引數OracleNull
- oracle sql_not exists與null的測試OracleSQLNull
- 了不起的 “filter(NULL IS NOT NULL)”FilterNull
- javascrit中undefined和null的區別JavaUndefinedNull
- Shell指令碼中的 /Dev/Null 用途指令碼devNull
- js中null和undefined的區別JSNullUndefined
- js中undefined和null的區別JSUndefinedNull
- mysql中null與“空值”的坑MySqlNull
- MySQL中的NULL和空串比較MySqlNull
- SQL中的Null深入研究分析SQLNull
- CHECK約束中的NULL條件Null
- SQL中關於NULL的程式碼SQLNull
- ((NULL) null).printNULL();((NULL) null).printnull();Null