Индекс и значение NULL

dbstalker, 20 июля

Как известно из документации, индекс на основе B* - дерева не содержит неопределенных значений. То есть, если таблица содержит записи, у которых все ключи индекса имеют значение NULL, то такие записи в индекс не попадают. Если хотя бы одно значение ключа не является NULL, такая запись попадает в индекс. Найдем простейшее доказательство этого утверждения.


SQL> create table my_table (a int, b int);

Table created.

SQL> create unique index my_table_index on my_table (a,b);

Index created.

SQL> insert into my_table values (1, 1) ;

1 row created.

SQL> insert into my_table values (1, NULL);

1 row created.

SQL> insert into my_table values (NULL, 1) ;

1 row created.

SQL> insert into my_table values (NULL, NULL);

1 row created.

SQL> insert into my_table values (NULL, NULL);

1 row created.

SQL> insert into my_table values (NULL, NULL);

1 row created.

SQL> insert into my_table values (NULL, NULL);

1 row created.

SQL> analyze index my_table_index  validate structure;

Index analyzed.

SQL> select name, lf_rows from index_stats;

NAME                              LF_ROWS
------------------------------ ----------
MY_TABLE_INDEX                          3

Как видим, количество листовых строк (значений в индексе) всего 3, хотя мы вставляли 7 строк, 4 строки из которых пустые.

Теперь проверим как работает уникальность индекса.

SQL> insert into my_table values (1, 1) ;
insert into my_table values (1, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.MY_TABLE_INDEX) violated


SQL> insert into my_table values (1, NULL);
insert into my_table values (1, NULL)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.MY_TABLE_INDEX) violated


SQL> insert into my_table values (NULL, 1) ;
insert into my_table values (NULL, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.MY_TABLE_INDEX) violated


SQL> insert into my_table values (NULL, NULL);

1 row created.

SQL> insert into my_table values (NULL, NULL);

1 row created.

SQL> insert into my_table values (NULL, NULL);

1 row created.

SQL> insert into my_table values (NULL, NULL);

1 row created.

Получается, что в таблицу можно вставлять сколько угодно пустых строк и уникальный индекс это переживет. А вот если хотя бы один ключ индекса непустой, вставить неуникальную запись не получится.

Давайте еще в дампе индекса посмотрим как хранятся пустые ключи.

SELECT object_id FROM user_objects WHERE object_name = 'MY_TABLE_INDEX';

OBJECT_ID
1423056

SELECT DBMS_ROWID.rowid_relative_fno (index_rowid) file_no, DBMS_ROWID.rowid_block_number (index_rowid) block_no FROM 
SELECT sys_op_lbid(1423056, 'L', my_table.ROWID) index_rowid FROM my_table);
FILE_NO	BLOCK_NO
18	94002
18	94002
18	94002

ALTER SYSTEM DUMP DATAFILE 18 BLOCK 94002;

…
row#0[8022] flag: ------, lock: 2, len=14, data:(6):  00 41 6f 22 00 00
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
row#1[8010] flag: ------, lock: 2, len=12, data:(6):  00 41 6f 22 00 01
col 0; len 2; (2):  c1 02
col 1; NULL
row#2[7998] flag: ------, lock: 2, len=12, data:(6):  00 41 6f 22 00 02
col 0; NULL
col 1; len 2; (2):  c1 02
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 18  minblk 94002 maxblk 94002

В дампе видно, что если хотя бы один ключ непустой, то индексная запись присутствует и ключ со значение NULL находится в листовом блоке. А вот индексных записей со всеми пустыми ключами нет.

Тэги: NULL, индексы

ОднаКнопка

 

Новый комментарий

Я не спамер: введите суму 1+6



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Нужен поставщик Дропшиппинг
10 декабря, 1 ответа
КИНО КАФЕ!!!!!!!!!!!!!!!!
10 декабря, 1 ответа