主键和Null看似没有多大的关系，因为一般的主键设置都是not null,但是把两者结合起来，会有很多意想不到的情况，说是意想不到是因为结果不在预期范围，但是如果明白了基本的原理，整个过程又在情理之中。 我们先来演示一下问题。 首先创建一个表，创建唯一性索引。

SQL> conn n1/n1

Connected. SQL> SQL> select*from cat;

no rows selected

SQL> create table test(x number,y number);

Table created.

SQL> create unique index ind_test on test(x,y);

Index created.

SQL> insert into test values(1,2);

1 row created.

再次插入重复的数据，这个肯定会抛错是毫无疑问的。

SQL> insert into test values(1,2);

insert into test values(1,2) * ERROR at line 1: ORA-00001: unique constraint (N1.IND_TEST) violated

然后我们开始测试null相关的场景。

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

1 row created.

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

1 row created.

插入两个Null值，也是可以的。

SQL> insert into test values(null,null);

1 row created.

再次插入两个null值，还是可以的。 SQL> insert into test values(null,null);

1 row created.

但是反过来再次插入1,null的时候就抛错了。

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

insert into test values(1,null) * ERROR at line 1: ORA-00001: unique constraint (N1.IND_TEST) violated

同理，null,1的场景也是如此。

SQL> insert into test values(null,1); insert into test values(null,1) * ERROR at line 1: ORA-00001: unique constraint (N1.IND_TEST) violated

再次插入两个null值。

SQL> insert into test values(null,null);

1 row created.

查看表test中的数据，如下：

SQL> select *from test;

         X          Y

---------- ----------          1          2          1                     1

 

6 rows selected. 可以看到有6行。null值列看不到任何显示。 为了标识，我们打印出rownum来。

SQL> select rownum,x,y from test;

    ROWNUM          X          Y

---------- ---------- ----------          1          1          2          2          1          3                     1          4          5          6

6 rows selected.

测试完了null值相关的，我们来看看空串''的情况。 插入''的时候就会抛错。

SQL> insert into test values(1,'');

insert into test values(1,'') * ERROR at line 1: ORA-00001: unique constraint (N1.IND_TEST) violated

插入两个空串，和null的效果是一样的。

SQL> insert into test values('','');

1 row created.

null和空串组合，也没有问题。

SQL> insert into test values(null,'');

1 row created.

SQL> insert into test values('','');

1 row created.

再次查看数据，null值的数据行明显增多。

SQL> select rownum,x,y from test;

    ROWNUM          X          Y

---------- ---------- ----------          1          1          2          2          1          3                     1          4          5          6          7          8          9

9 rows selected.

我们可以再进一步，查看null值的长度，使用length()

SQL> select rownum,x,length(x),y,length(y) from test;

    ROWNUM          X  LENGTH(X)          Y  LENGTH(Y)

---------- ---------- ---------- ---------- ----------          1          1          1          2          1          2          1          1          3                                1          1          4          5          6          7          8          9

9 rows selected.

可以看到null值对应的length没有任何显示。 如果用=来匹配空串，和null的效果一样，匹配不了。

SQL> select *from test where x='';

no rows selected

我们还是来看看dump的信息吧，对于null列dump的结果就是null

  1* select rownum,x,y,dump(x) from test

SQL> /

    ROWNUM          X          Y DUMP(X)

---------- ---------- ---------- ------------------------------          1          1          2 Typ=2 Len=2: 193,2          2          1            Typ=2 Len=2: 193,2          3                     1 NULL          4                       NULL          5                       NULL          6                       NULL          7                       NULL          8                       NULL          9                       NULL

9 rows selected.

感觉null值还是一个很有意思的话题，如果在查询中使用了 where xxx is null的方式，就不会走索引扫描，

而如果表中没有not null的约束，这可能会牵扯到一个全表扫描的案例  我们还是创建一个新表a,然后字段Object_id上没有not null约束

SQL> create table a as select object_id,object_name,object_type from dba_objects;

Table created.

SQL> desc a

 Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  OBJECT_ID                                                      NUMBER  OBJECT_NAME                                               VARCHAR2(128)  OBJECT_TYPE                                               VARCHAR2(19)

SQL> analyze table a compute statistics;

Table analyzed. Dbms_stats.gather_table_stats; SQL> create unique index ind_a on a(object_id); Index created.

SQL> set autot traceonly exp

如果根据object_id来查询，是会走唯一性扫描。 但是如果查看所有object_id的值，就会走全表扫描。如果查看object_id为null的行，发现时0条。 如果加入了not null约束，就会走fast full scan了。 可见null值对于索引扫描的影响确实是非常巨大，需要在写sql语句的时候提前注意到这个问题。

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