空值对索引的影响

时间:2022-11-01 04:43:26

我们首先做一些测试数据:

SQL> create table t(x int, y int);

 

Table created

 

请注意,这里我对表t做了一个唯一(联合)索引:

SQL> create unique index t_idx on t(x,y);

 

Index created

 

SQL> insert into t values(1,1);

 

1 row inserted

 

SQL> insert into t values(1,NULL);

 

1 row inserted

 

SQL> insert into t values(NULL,1);

 

1 row inserted

 

SQL> insert into t values(NULL,NULL);

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

下面我们分析一下索引:

SQL> analyze index t_idx validate structure;

 

Index analyzed

 

SQL> select name,lf_rows from index_stats;

 

NAME                              LF_ROWS

------------------------------ ----------

T_IDX                                   3

 

SQL>

然后,我们就可以看到,当前的索引中仅仅保存了3行数据。

请注意,上面我们插入并提交了四行数据。

所以,这里就有一个结论:

Oracle的索引不保存全部为空的行。

 

 

我们继续插入数据,现在再插入几行全部为空的行:

SQL> insert into t values(NULL,NULL);

 

1 row inserted

 

SQL> insert into t values(NULL,NULL);

 

1 row inserted

我们看到这样的插入,居然没有违反前面我们设定的唯一约束(unique on t(x,y)),

所以,这里我们又得出一个结论:

Oracle认为 NULL<>NULL ,进而 (NULL,NULL)<>(NULL,NULL)

换句话说,Oracle认为空值(NULL)不等于任何值,包括空值也不等于空值。

 

我们看到下面的插入会违反唯一约束(DEMO.T_IDX),这个很好理解了,因为它不是全部为空的值,即它不是(NULL,NULL),只有全部为空的行才被认为是不同的行:

SQL> insert into t values(1,null);

 

insert into t values(1,null)

 

ORA-00001: 违反唯一约束条件 (DEMO.T_IDX)

 

SQL> insert into t values(null,1);

 

insert into t values(null,1)

 

ORA-00001: 违反唯一约束条件 (DEMO.T_IDX)

 

SQL>

 

请看下面的例子:

SQL> select x,y,count(*) from t group by x,y;

 

    X        Y   COUNT(*)

----- -------- ----------

                        3

             1          1

    1                   1

    1        1          1

Executed in 0.03 seconds

 

SQL> select x,y,count(*) from t where x is null and y is null group by x,y;

 

   X       Y   COUNT(*)

---- ------- ----------

                      3

 

Executed in 0.01 seconds

 

SQL>

SQL> select x,y,count(*) from t group by x,y having count(*)>1;

 

     X                    Y   COUNT(*)

------ -------------------- ----------

                                     3

 

Executed in 0.02 seconds

SQL>

可以看见,完全为空的行有三行,这里我们又可以得出一个结论:

oracle在group by子句中认为完全为空的行是相同的行

换句话说,在group by子句中,oracle认为(NULL,NULL)=(NULL,NULL)

 

 

 

下面的语句,使用了复合索引(x,y)的前导列,通常这样的查询是会使用索引的,我们看看下面的例子:

select * from t where x is null;

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  TABLE ACCESS FULL   | T           |       |       |       |

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("T"."X" IS NULL)

Note: rule based optimization

 

14 rows selected

 

Executed in 0.06 seconds

 

我们看到上面的查询并没有使用索引,那么对比一下不使用控制的情况:

对比一下下面的查询:

select * from t where x=1;

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  INDEX RANGE SCAN    | T_IDX       |       |       |       |

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T"."X"=1)

Note: rule based optimization

 

14 rows selected

 

Executed in 0.04 seconds

这个查询(where x=1)如我们所希望的那样使用了t_idx(x,y)复合索引,这里我们可以得出一个结论:

在使用IS NULL 和 IS NOT NULL条件的时候,Oracle不使用索引(因为Oracle的索引不存储空值,详细请参见前面的相关内容)

 

那么我们如何使用空值的比较条件呢?

首先,尽量不在前导列上使用空值,请看下面的例子:

select * from t where x=1 and y is null;

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  INDEX RANGE SCAN    | T_IDX       |       |       |       |

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T"."X"=1)

       filter("T"."Y" IS NULL)

Note: rule based optimization

 

15 rows selected

 

select * from t where x is null and y=1;

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  TABLE ACCESS FULL   | T           |       |       |       |

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("T"."Y"=1 AND "T"."X" IS NULL)

Note: rule based optimization

 

14 rows selected

 

还有一个可以变通的方法,即我们在创建表的时候,为每个列都指定为非空约束(NOT NULL),并且在必要的列上使用default值,如:

SQL> create table lunar(

  2   c1 varchar2(10) default 'empty'

  3     constraint  c1_notnull not null,

  4   c2 number(10) default 0

  5     constraint c2_notnull not null,

  6   c3 date default to_date('20990101','yyyymmdd')

  7     constraint c3_notnull not null);

 

表已创建。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c1) values('first');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c2) values(99);

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c3) values(sysdate);

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c1,c3) values('ok',sysdate);

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> insert into lunar(c2,c1) values(999,'hello');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

SQL> commit;

 

提交完成。

 

已用时间:  00: 00: 00.00

SQL> select * from lunar;

 

C1                 C2 C3

---------- ---------- ----------

first               0 01-1月 -99

empty              99 01-1月 -99

empty               0 19-10月-04

ok                  0 19-10月-04

hello             999 01-1月 -99

 

已用时间:  00: 00: 00.00

SQL> select c1,c2,to_char(c3,'yyyy-mm-yy') from lunar;

 

C1                 C2 TO_CHAR(C3

---------- ---------- ----------

first               0 2099-01-99

empty              99 2099-01-99

empty               0 2004-10-04

ok                  0 2004-10-04

hello             999 2099-01-99

 

已用时间:  00: 00: 00.00

SQL>

然后我们再像使用一般的列那样,使用他们,并且合理的为他们建立索引相信就可以很好的提高应用的查询效率。

 

评论

# yangtingkun 发表于2005-06-09 11:28:00  IP: 61.186.252.*
总结的很好。

但是对于“在使用IS NULL 和 IS NOT NULL条件的时候,Oracle不使用索引”这句话,说的有点绝对。

即使不考虑BITMAP索引,复合索引也会存储空值
SQL> create table tt (col1 number, col2 number);

表已创建。

SQL> create index ind_tt_col1_col2 on tt (col1, col2);

索引已创建。

SQL> insert into tt values (1, null);

已创建 1 行。

SQL> commit;

提交完成。

SQL> set autot on
SQL> select * from tt where col1 = 1 and col2 is null;

COL1 COL2
---------- ----------
1


Execution Plan
----------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'IND_TT_COL1_COL2' (NON-UNIQUE)

对于单列索引,如果指定IS NOT NULL也是有可能使用索引的。
# lunar 发表于2005-06-20 09:29:00  IP: 61.186.252.*
符合索引也不会存储完全为空的条目的,呵呵 :)