【DB笔试面试567】在Oracle中， IS NULL如何用到索引？
【DB笔试面试567】在Oracle中， IS NULL如何用到索引？
AiDBA宝典
发布于 2019-09-29 15:43:30
发布于 2019-09-29 15:43:30
文章被收录于专栏：小麦苗的DB宝专栏
♣
题目部分
在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程序员面试笔试宝典》，作者：李华荣。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2019-05-25，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
