空值对索引的影响

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

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;

NAMELF_ROWS

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

T_IDX3

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;

XYCOUNT(*)

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

3

11

11

111

Executed in 0.03 seconds

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

XYCOUNT(*)

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

3

Executed in 0.01 seconds

SQL>

SQL> select x,y,count(*) from tgroup by x,yhaving count(*)>1;

XYCOUNT(*)

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

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 twhere 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 twhere 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(

2c1 varchar2(10) default 'empty'

3constraintc1_notnull not null,

4c2 number(10) default 0

5constraint c2_notnull not null,

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

7constraint 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;

C1C2 C3

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

first0 01-1月-99

empty9901-1月-99

empty0 19-10月-04

ok0 19-10月-04

hello999 01-1月-99

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8102208/viewspace-468157/，如需转载，请注明出处，否则将追究法律责任。