Заметка про NULL

[miwa]

Спасибо за толковое объяснение насчет пустой строки. Мне, как неораклоиду, но все же разработчику БД было весьма интересно и познавательно. До этого из бешенной ругани с переходом на личности на форумах только знал, что такое имеет место быть :)

[qwazer]

Про NULL'ы в индексах все-таки хорошо бы написать.
По-моему самое главное правило при работе с NULL — не делайте булевы поля Nullable!

Очень часто сталкиваюсь.
А проектировщики проектируют базы в которых F_FLAG — булево поле, если NULL — то false, если не NULL то true.

Разработчик потом пишет
select * from table t where t.f_flag is not null

А то что запрос потом не разгонишь с помощью индексов никто не думает.

[abyrvalg]

Если народ проявит интерес, то обязательно будет вторая часть про индексы и прочее. Эта заметка — просто введение, чтобы определиться в терминах и проблематике.

[Shedal]

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

[Nichola]

Да, пишите пожалуйста. Интерес есть. Проявляется этим комментарием.

[eugenets]

Хмм, у меня тут вероятно пробел в представлениях.

Если есть такая ситуация: есть флаг, которым помечено небольшое количество данных в огромной таблице. Нужно находить все данные по этому флагу. Если мы сделаем его not null, то в индекс попадут все записи в таблице. Если же наоборот, обозначить false null'ом, то в индексе будут лежать только интересующие нас поля, что теоретически должно сэкономить память (если индекс в памяти) и упростить поиск по нему. Я не вижу технических преград для того чтобы is not null выбирать по индексу. Другой вопрос, делают так СУБД или нет.

[zerkms]

Если бы всё было так, как вы предлагаете, тогда этот индекс вообще бы никогда не работал. Потому что в случае с true — у нас cardinality == 1, и субд никогда за такими значениями в индекс не полезет, а в случае с false у нас null, которого в индексе нет — и опять фуллскан.

Но раз уж мы говорим об оракле — тогда тут просто нужно создать char(1) (T/F) и покрыть его bitmap индексом

[eugenets]

Ну, я предполагал, что в случае с true нам нужно пробежаться по всему индексу, что заметно быстрее, чем бежать по всей таблице (так как помеченных флагом сущностей по условию задачи сильно меньше, чем всех сущностей вообще, а те, которые не помечены, в индекс не попадают). И я не очень понимаю, почему бы СУБД не воспользоваться этим фактом (хотя допускаю, что могут быть причины, я бы с удовольствием их узнал).

[zerkms]

Если у вас будет bitmap индекс (который для таких случаев предназначен), тогда да — всё будет именно так (но всё равно — вам лучше будет хранить оба значения T/F, а на поле повешать NOT NULL).

В случае с B-Tree — любая современная субд решит выполнить фуллскан

[eugenets]

Спасибо. Вроде понятно, зачем bitmap index, и почему не будет работать B-Tree (вероятно потому что этот индекс просто не предназначен для такого использования, хотя технически проблем это сделать я не вижу).

Все равно, правда, непонятно, зачем false в индексе :)

[zhekappp]

На практике в oracle все несколько иначе.
Oracle будет использовать обычный b-tree индекс без проблем (если, конечно, статистика по таблице актуальна). Ведь план строится не из абстрактных предположений, а просто по подсчету cost операции. Количество чтений для full scan таблицы будет значительно больше количества чтений для доступа по индексу, если кол-во записей в индексе заметно менее общего количества записей в таблице, т.е как раз в нашем случае, когда null-значения в индексе не хранятся.
Что касается bitmap-индексов, то их использования в OLTP лучше избегать, они предназначены для OLAP-систем с практически «read only» таблицами. Это связано как с тем, что update bit-map индекса более ресурсоемок, так и с тем, что механизм блокировок отличен и мы получим низкое быстродействие при множестве одновременно изменяющих данных сесссий.

[zerkms]

Угу, на блокировки сам наступал, но в подавляющем большинстве ситуаций битмапы всё таки добро, чем зло

[eugenets]

Проверил на MySQL, читает по индексу. (B-Tree, так как bitmap в MySQL не поддерживается)

[zerkms]

Удивительно, гхм :-S

Спасибо за исследование

[abyrvalg]

Если есть индексированный (b-tree) столбец COL с распределением { null: 90%, 1: 5%, 2: 5% } и CBO знает об этом распределении, то в случае фильтра col is not null стоимость фуллскана по таблице будет гораздо выше, чем стоимость фуллскана по индексу. А CBO ленив… Кейс:

SQL> create table ttt( col int );
SQL> insert into ttt select null from dual connect by level <= 90;
SQL> insert into ttt select 1 from dual connect by level <= 5;
SQL> insert into ttt select 2 from dual connect by level <= 5;
SQL> commit;
SQL> select nvl(col, -1), count(*) from ttt group by nvl(col, -1);

NVL(COL,-1) COUNT(*)
----------- ----------
1 5
2 5
-1 90

SQL> create index ttt#ni#col on ttt(col);
SQL> exec dbms_stats.gather_table_stats( ownname => 'TEST', tabname => 'TTT', cascade => true );

select col from ttt where col is not null;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 10 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | TTT#NI#COL | 10 | 10 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

SQL> alter index ttt#ni#col unusable;

SQL> select col from ttt where col is not null;

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 10 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TTT | 10 | 10 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------


[ilnoor]

Никогда не делайте булевых полей в базе!

[zerkms]

А где их делать?

[abyrvalg]

А можно обоснование? Как сделать признак «запись удалена?», если он может принимать только два значения: «Да» и «Нет»?
В оракле хотя и нет типа данных BIT, но это легко реализуемо:

create table t( 
    id int
  , is_deleted varchar2(1) not null
  , constraint t#ck#is_deleted check(is_deleted in 'Y', 'N')
);
create bitmap index t#bi#is_deleted on t(is_deleted);

Я просто не вижу альтернатив...

[Shedal]

Отличная статья, спасибо!

По поводу подсветки, ИМХО все нормально выглядит. Если вас все же не устраивает тег source, попробуйте Source Code Highlighter.

[infom]

Резанул глаза только вот этот абзац:
Лично мне эквивалентность пустой строки и NULLа кажется вполне естественной и логичной. Само название «пустая строка» подразумавает отсутствие значения, пустоту, дырку от бублика. NULL, в общем-то, обозначает то же самое. Но здесь есть неприятное следствие: если про пустую строку можно с уверенностью сказать, что её длина равна нулю, то длина NULLа никак не определена. Поэтому, выражение length('') вернёт вам NULL, а не ноль, как вы, очевидно, ожидали. Ещё одна проблема: нельзя сравнивать с пустой строкой. Выражение val = '' вернёт состояние НЕИЗВЕСТНО, так как, по сути, эквивалентно val = NULL.
Вы ведь все время говорил что NULL это неизвестные данные, а тут скатываетесь опять в пустые данные!

[abyrvalg]

Я уже сижу засыпаю, наверное поэтому не очень понимаю, про что Вы. Здесь я хотел показать различия с ANSI SQL, где имеются отдельные понятия «пустая строка» и NULL («неизвестные данные»). Просто людей из неораклового мира отсутствие этого разделения часто приводит в замешательство, они начинают спорить, что такого не может быть, length('') должен вернуть ноль, это же очевидно!

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

[qehgt]

А пустые массивы или там файлы с нулевой длиной — тоже вам кажутся эквивалентными NULL'у?

Мне правда интересно.

[Nashev]

Это просто способ притянуть за уши хоть что-нибудь, чтоб считать для себя такое поведение Oracle хоть както оправданным. Без этого только чертыхаться останется, а так — можно относительно спокойно работать. По себе знаю.

[abyrvalg]

Нет, не кажутся :) Точно так же, как и пустой LOB не есть NULL.

[zhekappp]

Спасибо большое, статья очень понравилась.
Что касается индексов и null — для меня самый важный фактор это то, что null значения не хранятся в индексах.
На этом строится эффективная обработка больших объемов данных. Если в таблице есть поле, которое обозначает, обработана ли эта запись, то до обработки надо туда чего-то поставить отличное от null, а после успешной обработки сделать update этого поля в null. Ну и не забыть проиндексировать таблицу по этому полю, естественно.
Еще, наверное, можно упомянуть, что если поле is null и находится в конце строки, то оно не занимает место. За счет этого операция по добавлению нового столбца проходит практически мгновенно.

[kingu]

Операция добавления нового столбца проходит мгновенно начиная с 11-чего-то там, при наличии DEFAULT значения.

[abyrvalg]

На самом деле, долго выбирал между «троичной», «тернарной» и «трёхзначной». Остановился на «трёхзначной»: мне почему-то чаще попадался именно этот термин, к тому же он созвучен с «многозначной».
Википедия говорит про то, что «Трои́чная ло́гика (трёхзначная логика) — один из видов многозначной логики», точно так же везде встречается про «Бинарная логика — Двоичная логика (двузначная логика)».
Я понимаю, что википедия и вообще интернеты — это не очень-то достоверный источник информации, но больше мне искать правильные термины сейчас негде. Переезд у меня :)

[windessy]

Oracle, как и многие другие базы данных, построен на стандарте SQL. Неудивительно, что большая часть совпадает :)
А сам Oracle здесь при том, что в описанных примерах есть специфические вещи именно для Oracle.

[Enrey]

Да уж, равенство пустой строки и null — действительно притча во языцех :))
Как сторонник запрещать null где только можно, дабы избегать излишней троичной логики, данная особенность прямо ужас как раздражает.

Хорошая статья! Даёшь следующую тему «краткое введение в olap»! :)

[surly]

Я полагаю, именно из-за этой нестандартной особенности приравнивать пустую строку к NULL строковый тип был назван нестандартно: VARCHAR2.
Сейчас было бы логично для Oracle ввести новый строковый тип — VARCHAR, для которого принять стандартное поведение ('' is not NULL), и для совместимости с унаследованным кодом оставить и тип VARCHAR2 (для которого оставить всё как есть, '' is NULL).

[barbalion]

«Если я правильно ошибаюсь», то VARCHAR2 отличается от стандартного VARCHAR тем, что может иметь переменную длину (но не более максимальной). Стандартный VARCHAR всегда фиксированной длины и при попытке присвоить значение меньшей длины, дополняет его пробелами до нужной длины.

[surly]

Вы ошибаетесь. Вы только что описали тип CHAR. И по стандарту, и в Oracle он ведет себя именно так: дополняет пробелами до заданной длины.

[barbalion]

Ой. Пора перечитать мануалы.

[abyrvalg]

Varchar2 (4000 байт максимум) появился, вроде бы, в седьмой версии, до этого был просто varchar (2000 байт максимум). Кажется, varchar хранил NULL в отдельном поле. Пожалуй, это и всё, про я знаю про varchar: оракл я увидел как раз с седьмой версии, когда этот тип данных стал уже неактуален.
Нынешняя ситуация с варчаром такая:

Do not use the VARCHAR data type. Use the VARCHAR2 data type instead. Although the VARCHAR data type is currently synonymous with VARCHAR2, the VARCHAR data type is scheduled to be redefined as a separate data type used for variable-length character strings compared with different comparison semantics.

[barbalion]

Ну, например, вот введение в OLAP. Не про реализацию Оракл, правда. Про Оракл если бы написали, я бы тоже с удовольствием почитал.

[GrossHo]

NULL с пустой строкой почему не стоило путать — при операциях с NULL теряется типизация объекта. Хорошо еще, что SQL не обладает кучей типов, характерных для императивных языков.

[Zerkella]

Спасибо, очень хорошая статья.

[ivs13]

«Даже выражение NULL != NULL не будет истинным. Кстати, ложным это выражение тоже не будет»

Любое сравнение с нуллом всегда ложно:

begin
  IF NULL != NULL THEN
    dbms_output.put_line('равно');
  ELSE
    dbms_output.put_line('не равно');
  END IF;  
end;

[abyrvalg]

К сожалению, ложно Ваше высказывание, а не сравнение с нуллом: download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements005.htm#sthref486

Кстати, это очень распространённый способ ошибочного теста: то, что значение не истинно, ещё не означает, что оно ложно (логика-то не булева).

[ivs13]

Мда, таки я не прав. Единственное, что меня оправдывает, это «A condition that evaluates to UNKNOWN acts almost like FALSE». Ну а то, что «a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN» держать в голове стоит, но в настоящем на это можно не обращать внимания.

[Ghool]

Очень хорошо пишете.
Пишите ещё (вижу, что вы спрашивали, в комментах интерес проявлен, а статей у вас — только эта и есть).

[abyrvalg]

Я почти сразу после этой публикации резко сменил сферу деятельности, поэтому доделать остальные части не успел. Недавно наткнулся на черновики, перечитал и, вздохнув, стёр их: отсутствие информации лучше, чем устаревшая информация.

[YurySS]

А никто не знает, это вот фича или баг:
exec test_bool( null or true ); — TRUE < — !!!
exec test_bool( null and false ); — FALSE < — !!!
Сам столкнулся:
begin
:res1 := '';
:res2 := '';
:res3 := '';
:res4 := '';
:res5 := '';
:res6 := '';
if (null) then
:res1 := 'goal';
end if;
if (true and null) then
:res2 := 'goal';
end if;
if (false and null) then
:res3 := 'goal';
end if;
if not (null) then
:res4 := 'goal';
end if;
if not (true and null) then
:res5 := 'goal';
end if;
if not (false and null) then
:res6 := 'goal';
end if;
end;

Уже после этого нашёл данную статью…

[ClayRing]

Фича. NULL - это неизвестное значение, оно может быть и true и false, но база этого не знает.

null or true

Всегда является true независимо от того что вы подставие вместо null.

null and false

Всегда является false независимо от того что вы подставите вместо null.