当主键碰到NULL

主键和Null看似没有多大的关系，因为一般的主键设置都是not null,但是把两者结合起来，会有很多意想不到的情况，说是意想不到是因为结果不在预期范围，但是如果明白了基本的原理，整个过程又在情理之中。我们先来演示一下问题。首先创建一个表，创建唯一性索引。SQL> conn n1/n1
Connected.
SQL>
SQL> select*from cat;no rows selectedSQL> 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
66 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
99 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
99 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 NULL9 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语句的时候提前注意到这个问题。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址