Monday, December 05, 2005
A null oddity
We were having the NULL conversation at work today. During the course of which I asserted that you could build a unique index on a nullable column, but that the nulls would not be indexed. My colleagues asserted that you couldn't. So some testing was done.
(I have only edited the username). So, technically, my assertion that you could create a unique index on a nullable column but that the nulls are ignored appears to be correct in the single column case, but not in the multi-column case. I decided to have a look at the actual data stored in the indexes.
This gave the following for the multi-column index.
For the single column index I get this.
So it appears that the answer to whether nulls are indexed or not depends to some extent on the number of columns in the index, (note that *all* null columns still aren't stored in the multiple column index) and the behaviour of multi-column unique indexes may not be quite what you expect.
4 Comments
SQL> drop table t1;
Table dropped.
SQL> create table t1(c1 number not null, c2 number);
Table created.
SQL> insert into t1(c1,c2) values(1,null);
1 row created.
SQL> insert into t1(c1,c2) values(2,null);
1 row created.
SQL> commit;
Commit complete.
SQL> create unique index i1 on t1(c2);
Index created.
SQL> insert into t1(c1,c2) values(2,null);
1 row created.
SQL> rollback;
Rollback complete.
SQL> drop index i1;
Index dropped.
SQL> create unique index i1 on t1(c1,c2);
Index created.
SQL> insert into t1(c1,c2) values(2,null);
insert into t1(c1,c2) values(2,null)
*
ERROR at line 1:
ORA-00001: unique constraint (USERNAME.I1) violated
SQL>
(I have only edited the username). So, technically, my assertion that you could create a unique index on a nullable column but that the nulls are ignored appears to be correct in the single column case, but not in the multi-column case. I decided to have a look at the actual data stored in the indexes.
SQL> insert into t1(c1,c2) values(2,1);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> CREATE INDEX I2 ON T1(C2);
Index created.
SQL> INSERT INTO T1 VALUES(3,NULL);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT HEADER_FILE,HEADER_BLOCK,BLOCKS
2 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='I1' AND OWNER=USER;
HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
1 39593 8
1 row selected.
SQL> ALTER SYSTEM DUMP DATAFILE 1 BLOCK 39594;
System altered.
SQL> SELECT HEADER_FILE,HEADER_BLOCK,BLOCKS
2 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='I2' AND OWNER=USER;
HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
1 39601 8
1 row selected.
SQL> ALTER SYSTEM DUMP DATAFILE 1 BLOCK 39602;
System altered.
SQL>
This gave the following for the multi-column index.
Leaf block dump
===============
header address 142746204=0x882225c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7986=0x1f32
kdxcoavs 7942
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12, data:(6): 00 40 76 fa 00 00
col 0; len 2; (2): c1 02
col 1; NULL
row#1[7998] flag: ------, lock: 0, len=14, data:(6): 00 40 76 fa 00 02
col 0; len 2; (2): c1 03
col 1; len 2; (2): c1 02
row#2[8012] flag: ------, lock: 0, len=12, data:(6): 00 40 76 fa 00 01
col 0; len 2; (2): c1 03
col 1; NULL
row#3[7986] flag: ------, lock: 2, len=12, data:(6): 00 40 76 fa 00 03
col 0; len 2; (2): c1 04
col 1; NULL
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 39594 maxblk 39594
For the single column index I get this.
Leaf block dump
===============
header address 146500188=0x8bb6a5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8024=0x1f58
kdxcoavs 7986
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 40 76 fa 00 02
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 39602 maxblk 39602
So it appears that the answer to whether nulls are indexed or not depends to some extent on the number of columns in the index, (note that *all* null columns still aren't stored in the multiple column index) and the behaviour of multi-column unique indexes may not be quite what you expect.
4 Comments:
Niall,
This is documented at:
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#18583
This is documented at:
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#18583
I just had this conversation with someone yesterday. They insisted that a unique index was the same as a PK. Of course, it's not. It's hard to grasp that a unique index can have multiple null values.
Also, check out the query plan for a SELECT where the column is null. It can't use the index for the WHERE clause (the "is null" will be a filter predicate, not an access predicate), since the nulls are not stored.
And, finally, DISTINCT_KEYS doesn't go up when you add null values.
Also, check out the query plan for a SELECT where the column is null. It can't use the index for the WHERE clause (the "is null" will be a filter predicate, not an access predicate), since the nulls are not stored.
And, finally, DISTINCT_KEYS doesn't go up when you add null values.
# posted by 
: 7:48 PM, December 06, 2005
: 7:48 PM, December 06, 2005
Wonders if this is related: http://pipetalk.quest-pipelines.com/default.asp?action=9&read=11659&fid=23
Post a Comment
# posted by : 12:25 AM, December 07, 2005
: 12:25 AM, December 07, 2005
