Nulls are not stored in indexes … most of the time
Nulls are not stored in indexes … most of the time
Posted by
This question got posed on Twitter today
I answered briefly on Twitter, but I thought I should give it some “meat” with a blog post.
Firstly, lets explore the commonly understood mechanism where in a conventional index, nulls are not stored and hence you can have multiple index entries (so to speak) where the value is null.
Here is my simple table T with a standard (ascending) unique index, and we can see immediately that there is no limit to the number of null values, where by “null” I mean that all indexed columns are null, that you can have in the table.
SQL> create table t ( x varchar2(10) );
Table created.
SQL>
SQL> create unique index ix1 on t ( x );
Index created.
SQL>
SQL> begin
2 for i in 1 .. 1000 loop
3 insert into t values (null);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
To see what has been stored in the index, we can get the OBJECT_ID for the index, perform a tree dump on that object and examine the trace file.
SQL> select object_id from user_objects where object_name = 'IX1';
OBJECT_ID
----------
194498
SQL> alter session set events 'immediate trace name treedump level 194498';
Session altered.
Trace file C:\ORACLE\diag\rdbms\db18\db18\trace\db18_ora_20504.trc
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
...
...
...
----- begin tree dump
leaf: 0x301070b 50398987 (0: row:0.0 avs:8000)
----- end tree dump
The key piece of information here is that the number of rows in the index is zero. The null values had no effect on the index, which is why there is no limit on them.
Before looking at nulls in a descending index, I’ll first look a non-null value in a descending index. I’ll recreate the table T with a varchar2, so I can put an easy to find piece of text in a row (my name).
SQL> create table t ( x varchar2(10) );
Table created.
SQL>
SQL> create unique index ix1 on t ( x desc );
Index created.
SQL>
SQL> insert into t values ('Connor');
1 row created.
SQL> commit;
Commit complete.
Now I’ll repeat the same tree dump, but explore a little further. The tree dump also contains the location of where this leaf block is stored. We call this the “block address”. There are supplied routines in DBMS_UTILITY to convert this into the file and block# where this leaf block is stored.
SQL> select object_id from user_objects where object_name = 'IX1';
OBJECT_ID
----------
194511
SQL> alter session set events 'immediate trace name treedump level 194511';
Session altered.
----- begin tree dump
leaf: 0x301070b 50398987 (0: row:1.1 avs:7982)
----- end tree dump
SQL> set serverout on
SQL> begin
2 dbms_output.put_line(dbms_utility.data_block_address_file(50398987));
3 dbms_output.put_line(dbms_utility.data_block_address_block(50398987));
4 end;
5 /
12
67339
PL/SQL procedure successfully completed.
Now I can dump the block to a trace file, and explore the contents.
SQL>
SQL> alter system dump datafile 12 block 67339;
System altered.
Leaf block dump
===============
header address 132096100=0x7dfa064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8020=0x1f54
kdxcoavs 7982
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8036
row#0[8020] flag: -------, lock: 2, len=16, data:(6): 03 00 09 d7 00 00
col 0; len 7; (7): bc 90 91 91 90 8d ff
----- end of leaf block Logical dump -----
The byte representation of the string “Connor” is “bc 90 91 91 90 8d” which we can see in the dump toward the bottom. Notice however, that there is also a trailing “ff” in the key. This is added to allow the index entries to be stored in descending order, as per the index specification (otherwise variable length data would not be sorted correctly, eg, “Conn” must come after “Connor”).
Now I can return to original question – I will try to store 2 rows, both being null, in a descending index.
SQL> create table t ( x varchar2(10) );
Table created.
SQL>
SQL> create unique index ix1 on t ( x desc );
Index created.
SQL>
SQL> insert into t values (null);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t values (null);
insert into t values (null)
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.IX1) violated
SQL> commit;
Commit complete.
The second row is rejected. I’ll do the same tree dump
SQL> select object_id from user_objects where object_name = 'IX1';
OBJECT_ID
----------
194542
SQL> alter session set events 'immediate trace name treedump level 194542';
Session altered.
----- begin tree dump
leaf: 0x3000b33 50334515 (0: row:1.1 avs:7988)
----- end tree dump
Notice that unlike the first example, the index contains a row now. The null was not ignored from the index. To see why, we obtain the index leaf block data as before.
SQL> set serverout on
SQL> begin
2 dbms_output.put_line(dbms_utility.data_block_address_file(50334515 ));
3 dbms_output.put_line(dbms_utility.data_block_address_block(50334515 ));
4 end;
5 /
12
2867
PL/SQL procedure successfully completed.
SQL>
SQL> alter system dump datafile 12 block 2867;
System altered.
Leaf block dump
===============
header address 418168932=0x18ecc064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 8026=0x1f5a
kdxcoavs 7988
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8036
row#0[8026] flag: -------, lock: 0, len=10, data:(6): 03 00 09 d7 00 00
col 0; len 1; (1): 00
----- end of leaf block Logical dump -----
and as per the example with “Connor”, a trailing value was added to the key to ensure that nulls are “first” (as opposed to being last by default in a SELECT statement). Hence the null value has resulted in a non-null value in the index. And for a unique index, that non-null cannot be duplicated.
Comments