在Oracle中， IS NULL如何用到索引？

DB宝 2019-05-25

1557

♣题目部分在Oracle中， IS NULL如何用到索引？

     
♣答案部分


IS NULL用于判断某一列中的值是否为空。当IS NULL作为WHERE条件的时候，该列是不会用到索引的，但是可以加伪列创建伪联合索引来使得IS NULL使用索引，看如下的例子：
 1SQL> CREATE TABLE TB_LHR_20160427(OBJ_ID,OBJ_NAME) AS  SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS;
 2Table created.
 3SQL> CREATE INDEX I_TB_OBJ_ID_0 ON TB_LHR_20160427(OBJ_ID);
 4Index created.
 5SQL> SET AUTOT ON
 6SQL> SELECT COUNT(*) FROM TB_LHR_20160427 WHERE OBJ_ID IS NULL;
 7  COUNT(*)
 8----------
 9         0
10Execution Plan
11----------------------------------------------------------
12Plan hash value: 220746883
13--------------------------------------------------------------------------------------
14| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
15--------------------------------------------------------------------------------------
16|   0 | SELECT STATEMENT   |                 |     1 |    13 |   122   (3)| 00:00:02 |
17|   1 |  SORT AGGREGATE    |                 |     1 |    13 |            |          |
18|*  2 |   TABLE ACCESS FULL| TB_LHR_20160427 |     5 |    65 |   122   (3)| 00:00:02 |
19--------------------------------------------------------------------------------------
20Predicate Information (identified by operation id):
21---------------------------------------------------
22   2 - filter("OBJ_ID" IS NULL)
23Note
24-----
25   - dynamic sampling used for this statement (level=2)
26SQL> CREATE INDEX I_TB_OBJ_ID ON TB_LHR_20160427(OBJ_ID,-1);
27Index created.
28SQL> SET AUTOTRACE TRACE EXP
29SQL> SET LINE 9999
30SQL> SELECT COUNT(*) FROM TB_LHR_20160427 WHERE OBJ_ID IS NULL;
31Execution Plan
32----------------------------------------------------------
33Plan hash value: 3872560566
34---------------------------------------------------------------------------------
35| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
36---------------------------------------------------------------------------------
37|   0 | SELECT STATEMENT  |             |     1 |    13 |    13   (0)| 00:00:01 |
38|   1 |  SORT AGGREGATE   |             |     1 |    13 |            |          |
39|*  2 |   INDEX RANGE SCAN| I_TB_OBJ_ID |     4 |    52 |    13   (0)| 00:00:01 |
40---------------------------------------------------------------------------------
41Predicate Information (identified by operation id):
42---------------------------------------------------
43   2 - access("OBJ_ID" IS NULL)
44Note
45-----
46   - dynamic sampling used for this statement (level=2)

& 说明：
有关NULL和索引的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141337/




本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。