Заметка про NULL

    Всем привет!
    Долго думал, что бы написать полезного про Оракл, перепробовал кучу тем. Каждый раз получалось слишком длинно, потому что уносило глубоко в дебри. Поэтому решил начать с максимально простой темы, чтобы оценить интерес аудитории и её отношение к моему стилю изложения материала (имхо, писатель из меня не очень). Несколько замечаний:
    • заметка относится к СУБД Oracle, но большинство написанного касается и других СУБД;
    • не получилось симпатично оформть код: тег source lang=«sql» подсвечивает слишком мало, как сделать по другому не в курсе. Если кто покажет, быстро всё приведу в более читабельный вид.
    Итак.

    Основные положения

    Специальное значение NULL означает отсутствие данных, констатацию того факта, что значение неизвестно. По умолчанию это значение могут принимать столбцы и переменные любых типов, если только на них не наложено ограничение NOT NULL. Также, СУБД автоматически добавляет ограничение NOT NULL к столбцам, включенным в первичный ключ таблицы.

    Основная особенность NULLа заключается в том, что он не равен ничему, даже другому NULLу. С ним нельзя сравнить какое-либо значение с помощью любых операторов: =, <, >, like… Даже выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при вычислении условий Oracle не ограничивается состояниями ИСТИНА и ЛОЖЬ. Из-за наличия элемента неопределённости в виде NULLа существует ещё одно состояние — НЕИЗВЕСТНО.

    Таким образом, Oracle оперирует не двухзначной, а трёхзначной логикой. Эту особенность заложил в свою реляционную теорию дедушка Кодд, а Oracle, являясь реляционной СУБД, полностью следует его заветам. Чтобы не медитировать над “странными” результатами запросов, разработчику необходимо знать таблицу истинности трёхзначной логики. Ознакомиться с ней можно, например, на английской википедии: Three-valued_logic.

    Для удобства сделаем процедуру, печатающую состояние булевого параметра:
    create procedure test_bool( p_bool boolean ) is
    begin
       case when p_bool = true  then dbms_output.put_line('TRUE');
            when p_bool = false then dbms_output.put_line('FALSE');
            else dbms_output.put_line('UNKNOWN');
       end case;
    end test_bool;

    и включим опцию печати сообщений на консоль:
    set serveroutput on

    Привычные операторы сравнения пасуют перед NULLом:
    exec test_bool( null  = null );  -- UNKNOWN
    exec test_bool( null != null );  -- UNKNOWN
    exec test_bool( null  = 'a'  );  -- UNKNOWN
    exec test_bool( null != 'a'  );  -- UNKNOWN


    Сравнение с NULLом


    Существуют специальные операторы IS NULL и IS NOT NULL, которые позволяют производить сравнения с NULLами. IS NULL вернёт истину, если операнд имеет значение NULL и ложь, если он им не является.

    select case when null is null then 'YES' else 'NO' end from dual; -- YES
    select case when 'a'  is null then 'YES' else 'NO' end from dual; -- NO

    Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:

    select case when 'a'  is NOT null then 'YES' else 'NO' end from dual; -- YES
    select case when null is NOT null then 'YES' else 'NO' end from dual; -- NO

    Кроме того, есть пара исключений из правил, касающихся сравнений с отсутствующими значениями. Во-первых, — это функция DECODE, которая считает два NULLа эквивалентными друг другу. Во-вторых, — это составные индексы: если два ключа содержат пустые поля, но все их непустые поля равны, то Oracle считает эти два ключа эквивалентными.

    DECODE идёт против системы:
    select decode( null
              , 1, 'ONE'
              , null, 'EMPTY' -- это условие будет истинным 
              , 'DEFAULT'
              )
     from dual;

    Пример с составными индексами находится в параграфе про индексы.

    Логические операции и NULL

    Обычно, состояние НЕИЗВЕСТНО обрабатывается так же, как ЛОЖЬ. Например, если вы выбираете строки из таблицы и вычисление условия x = NULL в предложении WHERE дало результат НЕИЗВЕСТНО, то вы не получите ни одной строки. Однако, есть и отличие: если выражение НЕ(ЛОЖЬ) вернёт истину, то НЕ(НЕИЗВЕСТНО) вернёт НЕИЗВЕСТНО. Логические операторы AND и OR также имеют свои особенности при обработке неизвестного состояния. Конкретика в примере ниже.

    В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ:
    select 1 from dual where dummy = null; -- запрос не вернёт записей

    Отрицание неизвестности даёт неизвестность:
    exec test_bool( not(null  = null) ); -- UNKNOWN
    exec test_bool( not(null != null) ); -- UNKNOWN
    exec test_bool( not(null  = 'a')  ); -- UNKNOWN
    exec test_bool( not(null != 'a')  ); -- UNKNOWN

    Оператор OR:
    exec test_bool( null or true  );   -- TRUE    <- !!!!!
    exec test_bool( null or false );   -- UNKNOWN
    exec test_bool( null or null  );   -- UNKNOWN

    Оператор AND:
    exec test_bool( null and true  );  -- UNKNOWN
    exec test_bool( null and false );  -- FALSE   <- !!!!!
    exec test_bool( null and null  );  -- UNKNOWN


    Операторы IN и NOT IN


    Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL
    create table t as select column_value a from table(sys.odcinumberlist(1,2,3,null));

    Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE).
    В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.
    set autotrace on

    Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL):
    select * from t where a in(1,2,null); -- вернёт [1,2]
    
    -- Predicate Information: 
    --   filter("A"=1 OR "A"=2 OR "A"=TO_NUMBER(NULL))

    Как видим, строка с NULLом не выбралась. Произошло это из-за того, что вычисление предиката "A"=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО. Для того, чтобы включить NULLы в результат запроса, придётся указать это явно:

    select * from t where a in(1,2) or a is null; -- вернёт [1,2,NULL]
    
    -- Predicate Information: 
    --    filter("A" IS NULL OR "A"=1 OR "A"=2)

    Попробуем теперь с NOT IN:
    select * from t where a not in(1,2,null); -- no rows selected
    
    -- Predicate Information:
    --   filter("A"<>1 AND "A"<>2 AND "A"<>TO_NUMBER(NULL))


    Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3:

       3<>1  AND  3<>2  AND  3<>to_number(NULL)
       \ /         \ /        \    /
       true  AND  true  AND  unknown
           \     /         /
            true    AND  unknown
                \       /
                 unknown

    Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.

    NULL и пустая строка


    Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.

    NULL и пустая строка эквивалентны:
    exec test_bool( '' is null );  -- TRUE


    непременный атрибут жёсткого спора:
    select count(*) comments_count, avg(c.vote) avg_vote
    from habr.comments c join habr.posts p on( c.post_id = p.id )
    where lower(p.title) like '%пробел%'
      and lower(p.title) like '%табуляци%';
    
      COMMENTS_COUNT  AVG_VOTE
     --------------- ---------
              100500      -100

    Если последовать завету классика и посмотреть в корень, то причину эквивалентности пуcтой строки и NULLа можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Oracle хранит строки таблицы в структуре, состоящей из заголовка, за которым следуют столбцы данных. Каждый столбец представлен двумя полями: длина данных в столбце (1 или 3 байта) и, собственно, сами данные. Если varchar2 имеет нулевую длину, то в поле с данными писать нечего, оно не занимает ни байта, а в поле с длиной записывается специальное значение 0xFF, обозначающее отсутствие данных. NULL представлен точно так же: поле с данными отсутствует, а в поле с длиной записывается 0xFF. Разработчики Оракла могли бы, конечно, разделить эти два состояния, но так уж издревле у них повелось.

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

    Длина пустой строки не определена:
    select length('') from dual; -- NULL

    Сравнение с пустой строкой невозможно:
    exec test_bool( 'a' != '' ); -- UNKNOWN

    Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…

    Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом '' (пустая строка), но не позволяют сохранить элемент с индексом NULL:

    declare
     procedure empty_or_null( p_val varchar2 ) is
       type tt is table of varchar2(1) index by varchar2(10);
       t tt;
     begin
       if p_val is not null then
         dbms_output.put_line('не пусто');
       else
         -- пытаемся создать элемент с индексом p_val
         t(p_val) := 'x';
         -- получилось! Значит, пустая строка
         dbms_output.put_line('пустая строка');
       end if;
     exception
       -- создать элемент с индексом p_val не получилось. Значит, NULL
       when others then dbms_output.put_line('NULL');
     end;
    begin
     empty_or_null( 'qwe' );  -- не пусто
     empty_or_null( '' );     -- пустая строка
     empty_or_null( NULL );   -- NULL
    end;

    Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.

    Математика NULLа

    Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.

    Задача. У Маши до замужества с Колей было неизвестное количество любовников. Коля знает, что после замужества у Маши был секс с ним, Сашей и Витей. Помогите найти Коле точное количество любовников Маши.

    Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:
    select decode( null + 10,  null, 'неизвестно', 'известно') a from dual; -- неизвестно
    select decode( null * 10,  null, 'неизвестно', 'известно') a from dual; -- неизвестно
    select decode( abs(null),  null, 'неизвестно', 'известно') a from dual; -- неизвестно
    select decode( sign(null), null, 'неизвестно', 'известно') a from dual; -- неизвестно

    С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.
    select null ||'AA'|| null ||'BB'|| null from dual; -- AABB


    NULL и агрегатные функции


    Почти все агрегатные функции, за исключением COUNT (и то не всегда), игнорируют пустые значения при расчётах. Если бы они этого не делали, то первый же залетевший NULL привёл бы результат функции к неизвестному значению. Возьмём для примера функцию SUM, которой необходимо просуммировать ряд (1, 3, null, 2). Если бы она учитывала пустые значения, то мы бы получили такую последовательность действий:
    1 + 3 = 4; 4 + null = null; null + 2 = null.
    Вряд ли вас устроит такой расчёт при вычислении агрегатов, ведь вы наверняка не это хотели получить. А какой бы был геморрой с построением хранилищ данных… Бррррр…

    Таблица с данными. Используется ниже много раз:
    create table agg( id int, n int );
    insert into agg values( 1, 1 );
    insert into agg values( 2, 3 );
    insert into agg values( 3, null );
    insert into agg values( 4, 2 );
    commit;

    Пустые значения игнорируются агрегатами:
    select sum(n) from agg; -- 6

    Функция подсчёта количества строк COUNT, если используется в виде COUNT(*) или COUNT(константа), будет учитывать пустые значения. Однако, если она используется в виде COUNT(выражение), то пустые значения будут игнорироваться.

    с константой:
    select count(*)    from agg; -- 4
    select count(1+1)  from agg; -- 4
    select count(user) from agg; -- 4

    С выражением:
    select count(n)      from agg; -- 3
    select count(id)     from agg; -- 4
    select count(abs(n)) from agg; -- 3

    Также, следует быть осторожным с функциями вроде AVG. Поскольку она проигнорирует пустые значения, результат по полю N будет равен (1+3+2)/3, а не (1+3+2)/4. Возможно, такой расчёт среднего вам не нужен. Для решения подобных проблем есть стандартное решение — воспользоваться функцией NVL:
    select avg(n)        from agg; -- (1 + 3 + 2) / 3 = 2
    select avg(nvl(n,0)) from agg; -- (1 + 3 + 0 + 2) / 4 = 1.5

    Агрегатные функции возвращают состояние НЕИЗВЕСТНО, если они применяются к пустому набору данных, либо если он состоит только из NULLов. Исключение составляют предназначенные для подсчёта количества строк функции REGR_COUNT и COUNT(выражение). Они в перечисленных выше случаях вернут ноль.

    Набор данных только из NULLов:
    select sum(n)          from agg where n is null; -- НЕИЗВЕСТНО
    select avg(n)          from agg where n is null; -- НЕИЗВЕСТНО
    select regr_count(n,n) from agg where n is null; -- 0
    select count(n)        from agg where n is null; -- 0

    Пустой набор данных:
    select sum(n)          from agg where 1 = 0; -- НЕИЗВЕСТНО
    select avg(n)          from agg where 1 = 0; -- НЕИЗВЕСТНО
    select regr_count(n,n) from agg where 1 = 0; -- 0
    select count(n)        from agg where 1 = 0; -- 0

    NULL в OLAP


    Очень коротко ещё об одной особенности, связанной с агрегатами. В многомерных кубах NULL в результах запроса может означать как отсутствие данных, так и признак группировки по измерению. Самое противное, что на глаз эти две его ипостаси никак не различишь. К счастью, есть специальные функции GROUPING и GROUPING_ID, у которых глаз острее. GROUPING(столбец) вернёт единицу, если NULL в столбце измерения означает признак группировки по этому столбцу и ноль, если там содержится конкретное значение (в частности, NULL). Функция GROUPING_ID — это битовый вектор из GROUPINGов, в этой заметке она точно лишняя.

    В общем, такая вот краткая и сумбурная информация про дуализм NULLа в многомерном анализе. Ниже пример использования GROUPING, а за подробностями велкам ту Data Warehousing Guide, глава 21.

    Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:
    set null [NULL]

    Проверяем дуализм NULLа в многомерном кубе:
    with t as (
         -- Иван купил 1 кефир
         select 'IVAN' customer, 'KEFIR'  product, 1 qty from dual union all
         -- неизвестный покупатель купил 2 молока
         select NULL   customer, 'MOLOKO' product, 2 qty from dual union all
         -- Иван купил 2 молока
         select 'IVAN' customer, 'MOLOKO' product, 2 qty from dual
         )
    select customer cust, grouping(customer) grp_c
         , product prod,  grouping(product)  grp_p
         , sum(qty) qty
    from t
    group by cube(customer, product)
    order by grp_c, grp_p;
    
    CUST GRP_C PROD   GRP_P  QTY
    ---- ----- ------ ----- ----
    [NULL]   0 MOLOKO     0    2  -- безымянный клиент купил 2 упаковки молока
    IVAN     0 KEFIR      0    1  -- Иван купил 1 упаковку кефира
    IVAN     0 MOLOKO     0    2  -- Иван купил 2 упаковки молока
    IVAN     0 [NULL]     1    3  -- Иван купил 3 упаковки всех товаров
    [NULL]   0 [NULL]     1    2  -- безымянный клиент купил 2 упаковки всех товаров
    [NULL]   1 MOLOKO     0    4  -- все клиенты купили 4 упаковки молока
    [NULL]   1 KEFIR      0    1  -- все клиенты купили 1 упаковку кефира
    [NULL]   1 [NULL]     1    5  -- все клиенты купили 5 упаковок всех товаров


    Что осталось за кадром


    Получилось довольно длинно, поэтому часть информации пришлось вырезать. А именно:
    • NULL в индексах (обычные, составные, бинарные, функциональные). Там у меня больше листингов, чем текста;
    • разбор функций для работы с NULLами: документированных и не очень;
    • работа с NULLами в запросах (всяческие кейсы)
    • может, ещё какие-то моменты забыл упомянуть, пишите в комментарии, добавлю.
    Критика, дополнения и намёки на некомпетентность автора (с отсылкой в доку) приветствуются. Собственно, для этого всё и затевалось.
    Поделиться публикацией
    Ой, у вас баннер убежал!

    Ну. И что?
    Реклама
    Комментарии 44
    • +1
      Спасибо за толковое объяснение насчет пустой строки. Мне, как неораклоиду, но все же разработчику БД было весьма интересно и познавательно. До этого из бешенной ругани с переходом на личности на форумах только знал, что такое имеет место быть :)
      • +10
        Про NULL'ы в индексах все-таки хорошо бы написать.
        По-моему самое главное правило при работе с NULL — не делайте булевы поля Nullable!

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

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

        А то что запрос потом не разгонишь с помощью индексов никто не думает.
        • +2
          Если народ проявит интерес, то обязательно будет вторая часть про индексы и прочее. Эта заметка — просто введение, чтобы определиться в терминах и проблематике.
          • +4
            А вы не ждите, пока народ проявит интерес :) все же, данная статья рассчитана на начинающих, а вот про индексы, оптимизацию, да и вообще про более сложные вещи, — было бы интересно почитать уже более опытной части юзеров.
            • +3
              Да, пишите пожалуйста. Интерес есть. Проявляется этим комментарием.
            • 0
              Хмм, у меня тут вероятно пробел в представлениях.

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

                Но раз уж мы говорим об оракле — тогда тут просто нужно создать char(1) (T/F) и покрыть его bitmap индексом
                • 0
                  Ну, я предполагал, что в случае с true нам нужно пробежаться по всему индексу, что заметно быстрее, чем бежать по всей таблице (так как помеченных флагом сущностей по условию задачи сильно меньше, чем всех сущностей вообще, а те, которые не помечены, в индекс не попадают). И я не очень понимаю, почему бы СУБД не воспользоваться этим фактом (хотя допускаю, что могут быть причины, я бы с удовольствием их узнал).
                  • 0
                    Если у вас будет bitmap индекс (который для таких случаев предназначен), тогда да — всё будет именно так (но всё равно — вам лучше будет хранить оба значения T/F, а на поле повешать NOT NULL).

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

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

                            Спасибо за исследование
                            • +2
                              Если есть индексированный (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 |
                              --------------------------------------------------------------------------
                  • 0
                    Никогда не делайте булевых полей в базе!
                    • 0
                      А где их делать?
                      • +2
                        А можно обоснование? Как сделать признак «запись удалена?», если он может принимать только два значения: «Да» и «Нет»?
                        В оракле хотя и нет типа данных 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);
                        
                        Я просто не вижу альтернатив...
                    • 0
                      Отличная статья, спасибо!

                      По поводу подсветки, ИМХО все нормально выглядит. Если вас все же не устраивает тег source, попробуйте Source Code Highlighter.
                      • +2
                        Резанул глаза только вот этот абзац:
                        Лично мне эквивалентность пустой строки и NULLа кажется вполне естественной и логичной. Само название «пустая строка» подразумавает отсутствие значения, пустоту, дырку от бублика. NULL, в общем-то, обозначает то же самое. Но здесь есть неприятное следствие: если про пустую строку можно с уверенностью сказать, что её длина равна нулю, то длина NULLа никак не определена. Поэтому, выражение length('') вернёт вам NULL, а не ноль, как вы, очевидно, ожидали. Ещё одна проблема: нельзя сравнивать с пустой строкой. Выражение val = '' вернёт состояние НЕИЗВЕСТНО, так как, по сути, эквивалентно val = NULL.
                        Вы ведь все время говорил что NULL это неизвестные данные, а тут скатываетесь опять в пустые данные!
                        • +1
                          Я уже сижу засыпаю, наверное поэтому не очень понимаю, про что Вы. Здесь я хотел показать различия с ANSI SQL, где имеются отдельные понятия «пустая строка» и NULL («неизвестные данные»). Просто людей из неораклового мира отсутствие этого разделения часто приводит в замешательство, они начинают спорить, что такого не может быть, length('') должен вернуть ноль, это же очевидно!

                          А если вы про корявость текста, то с радостью бы исправил его на более благозвучный.
                          • 0
                            А пустые массивы или там файлы с нулевой длиной — тоже вам кажутся эквивалентными NULL'у?

                            Мне правда интересно.
                            • +2
                              Это просто способ притянуть за уши хоть что-нибудь, чтоб считать для себя такое поведение Oracle хоть както оправданным. Без этого только чертыхаться останется, а так — можно относительно спокойно работать. По себе знаю.
                              • +1
                                Нет, не кажутся :) Точно так же, как и пустой LOB не есть NULL.
                          • +1
                            Спасибо большое, статья очень понравилась.
                            Что касается индексов и null — для меня самый важный фактор это то, что null значения не хранятся в индексах.
                            На этом строится эффективная обработка больших объемов данных. Если в таблице есть поле, которое обозначает, обработана ли эта запись, то до обработки надо туда чего-то поставить отличное от null, а после успешной обработки сделать update этого поля в null. Ну и не забыть проиндексировать таблицу по этому полю, естественно.
                            Еще, наверное, можно упомянуть, что если поле is null и находится в конце строки, то оно не занимает место. За счет этого операция по добавлению нового столбца проходит практически мгновенно.
                            • 0
                              Операция добавления нового столбца проходит мгновенно начиная с 11-чего-то там, при наличии DEFAULT значения.
                            • +1
                              А при чём тут Oracle? Большая часть того, что вы сказали, относится и к к другим реляционным СУБД.

                              ЗЫ: Кстати, логика бывает двоичной или бинарной, но никак не «двузначной». А «трёхзначная» — она на самом деле троичная или тернарная. Это вполне себе устоявшиеся в русском языке.
                              • 0
                                …термины.
                                • +2
                                  На самом деле, долго выбирал между «троичной», «тернарной» и «трёхзначной». Остановился на «трёхзначной»: мне почему-то чаще попадался именно этот термин, к тому же он созвучен с «многозначной».
                                  Википедия говорит про то, что «Трои́чная ло́гика (трёхзначная логика) — один из видов многозначной логики», точно так же везде встречается про «Бинарная логика — Двоичная логика (двузначная логика)».
                                  Я понимаю, что википедия и вообще интернеты — это не очень-то достоверный источник информации, но больше мне искать правильные термины сейчас негде. Переезд у меня :)
                                • +1
                                  Oracle, как и многие другие базы данных, построен на стандарте SQL. Неудивительно, что большая часть совпадает :)
                                  А сам Oracle здесь при том, что в описанных примерах есть специфические вещи именно для Oracle.
                                • +1
                                  Да уж, равенство пустой строки и null — действительно притча во языцех :))
                                  Как сторонник запрещать null где только можно, дабы избегать излишней троичной логики, данная особенность прямо ужас как раздражает.

                                  Хорошая статья! Даёшь следующую тему «краткое введение в olap»! :)
                                  • +1
                                    Я полагаю, именно из-за этой нестандартной особенности приравнивать пустую строку к NULL строковый тип был назван нестандартно: VARCHAR2.
                                    Сейчас было бы логично для Oracle ввести новый строковый тип — VARCHAR, для которого принять стандартное поведение ('' is not NULL), и для совместимости с унаследованным кодом оставить и тип VARCHAR2 (для которого оставить всё как есть, '' is NULL).
                                    • 0
                                      «Если я правильно ошибаюсь», то VARCHAR2 отличается от стандартного VARCHAR тем, что может иметь переменную длину (но не более максимальной). Стандартный VARCHAR всегда фиксированной длины и при попытке присвоить значение меньшей длины, дополняет его пробелами до нужной длины.
                                      • +1
                                        Вы ошибаетесь. Вы только что описали тип CHAR. И по стандарту, и в Oracle он ведет себя именно так: дополняет пробелами до заданной длины.
                                        • 0
                                          Ой. Пора перечитать мануалы.
                                      • +2
                                        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.
                                      • 0
                                        Ну, например, вот введение в OLAP. Не про реализацию Оракл, правда. Про Оракл если бы написали, я бы тоже с удовольствием почитал.
                                      • 0
                                        NULL с пустой строкой почему не стоило путать — при операциях с NULL теряется типизация объекта. Хорошо еще, что SQL не обладает кучей типов, характерных для императивных языков.
                                        • +2
                                          Спасибо, очень хорошая статья.
                                          • 0
                                            «Даже выражение NULL != NULL не будет истинным. Кстати, ложным это выражение тоже не будет»

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

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

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

                                              Кстати, это очень распространённый способ ошибочного теста: то, что значение не истинно, ещё не означает, что оно ложно (логика-то не булева).
                                              • +2
                                                Мда, таки я не прав. Единственное, что меня оправдывает, это «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» держать в голове стоит, но в настоящем на это можно не обращать внимания.
                                            • 0
                                              Очень хорошо пишете.
                                              Пишите ещё (вижу, что вы спрашивали, в комментах интерес проявлен, а статей у вас — только эта и есть).
                                              • 0
                                                Я почти сразу после этой публикации резко сменил сферу деятельности, поэтому доделать остальные части не успел. Недавно наткнулся на черновики, перечитал и, вздохнув, стёр их: отсутствие информации лучше, чем устаревшая информация.

                                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                              Самое читаемое