【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程序员面试笔试宝典》，作者：李华荣。
