创建包含null值index

在不少时候我们需要对一个值是否是null的查询,根据Oracle的特点,我们单纯在在这个列上创建一个index不能满足这个需求,因为b-tree index中就是不包含null列.通过创建含常数列的复合index可以满足该需求
数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

创建测试表

SQL> create table t_xifenfei 
  2  as
  3  select * from dba_objects;

Table created.

SQL> desc t_xifenfei;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 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)

创建可能含null列index

SQL> create index ind_object_id on t_xifenfei(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T_XIFENFEI',cascade => true);

PL/SQL procedure successfully completed.

查看执行计划

SQL> SET AUTOT TRACE EXP stat
SQL> SELECT * FROM T_XIFENFEI WHERE OBJECT_ID IS NULL;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    95 |   159   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    95 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID" IS NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        695  consistent gets
          0  physical reads
          0  redo size
        995  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

可以看到我们创建的一个普通的index,查询判断是否为null的时候,没有被应用该index,而是直接使用全表扫描.

创建支持null index

SQL> drop index ind_object_id ;

Index dropped.

SQL> create index ind_object_id on t_xifenfei(object_id,0);  

Index created.

SQL>  exec dbms_stats.gather_table_stats(user,'T_XIFENFEI',cascade => true);

PL/SQL procedure successfully completed.

查看执行计划

SQL> SELECT * FROM T_XIFENFEI WHERE OBJECT_ID IS NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 804765899

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    95 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI    |     1 |    95 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID" IS NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        995  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

这里使用了index,并且执行中逻辑读大幅度下降,很大程度提高了程序的执行效率,逻辑读从695降低为2.

原因分析
建立一个包含列和常数的复合index,可以实现该需求,根据b-tree index的特点,只有当index中包含的列都为null的时候,才不会别在index中记录,因为设置了index中包含的常数列,所以就是列为null,也会被包含在该index中,从而查询null值的时候依然可以使用到该index