【DB筆試面試567】在Oracle中， IS NULL如何用到索引？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中， IS NULL如何用到索引？
♣
答案部分
IS NULL用於判斷某一列中的值是否為空。當IS NULL作為WHERE條件的時候，該列是不會用到索引的，但是可以加偽列創建偽聯合索引來使得IS NULL使用索引，看如下的例子：
SQL> CREATE TABLE TB_LHR_20160427(OBJ_ID,OBJ_NAME) AS SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS; Table created. SQL> CREATE INDEX I_TB_OBJ_ID_0 ON TB_LHR_20160427(OBJ_ID); Index created. SQL> SET AUTOT ON SQL> SELECT COUNT(*) FROM TB_LHR_20160427 WHERE OBJ_ID IS NULL; COUNT(*) ---------- 0 Execution Plan ---------------------------------------------------------- Plan hash value: 220746883 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 122 (3)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | TABLE ACCESS FULL| TB_LHR_20160427 | 5 | 65 | 122 (3)| 00:00:02 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJ_ID" IS NULL) Note ----- - dynamic sampling used for this statement (level=2) SQL> CREATE INDEX I_TB_OBJ_ID ON TB_LHR_20160427(OBJ_ID,-1); Index created. SQL> SET AUTOTRACE TRACE EXP SQL> SET LINE 9999 SQL> SELECT COUNT(*) FROM TB_LHR_20160427 WHERE OBJ_ID IS NULL; Execution Plan ---------------------------------------------------------- Plan hash value: 3872560566 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 13 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX RANGE SCAN| I_TB_OBJ_ID | 4 | 52 | 13 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJ_ID" IS NULL) Note ----- - dynamic sampling used for this statement (level=2)
& 說明：
有關NULL和索引的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141337/
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
