Как NULL-значения влияют на производительность поиска в базе данных?

В Oracle значения NULL не индексируются, то есть следующий запрос:

SELECT  *
FROM    table
WHERE   column IS NULL

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

Более того, этот запрос:

SELECT  column
FROM    table
ORDER BY
        column

тоже будет использовать полный скан таблицы и сортировку по той же причине.

Если ваши значения не должны содержать NULL, то рекомендуется пометить столбец как NOT NULL.

Краткий ответ: да, с оговорками!

Основная проблема с нулевыми значениями и производительностью связана с форвардными ссылками.

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

...но давайте предположим, что страница заполнилась, и теперь эта строка оказалась среди других строк. Всё еще нормально...

...пока строка не обновится, и нулевое значение не станет чем-то иным. Размер строки увеличивается за пределы доступного ей пространства, и движок БД должен что-то с этим делать.

Самый быстрый вариант для сервера — переместить строку с этой страницы на другую, заменив запись о строке форвардной ссылкой. К сожалению, это требует дополнительного поиска при выполнении запроса: один для нахождения естественного места строки и один для нахождения её текущего местоположения.

Таким образом, короткий ответ на ваш вопрос: да, сделать эти поля ненулевыми поможет улучшить производительность поиска. Особенно это актуально, если нулевые поля в записях, которые вы ищете, часто обновляются до ненулевых значений.

Конечно, существуют и другие недостатки (в частности, I/O, хотя и в незначительной степени — глубина индекса), связанные с более крупными наборами данных, а также возникающие в приложении проблемы с запретом нулей в полях, которые концептуально их требуют, но это уже другая история 😃

Добрый день!

В дополнение к комментарию Дэвида Олдриджа по поводу принятого ответа Квасноя хочу подчеркнуть, что утверждение:

этот запрос:

SELECT * FROM table WHERE column IS NULL

всегда будет использовать полное сканирование таблицы

— не является верным. Вот контрпример, в котором используется индекс с литералом:

SQL> create table mytable (mycolumn)
  2  as
  3   select nullif(level,10000)
  4     from dual
  5  connect by level <= 10000
  6  /

Table created.

SQL> create index i1 on mytable(mycolumn,1)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true)

PL/SQL procedure successfully completed.

SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ *
  2    from mytable
  3   where mycolumn is null
  4  /

  MYCOLUMN
----------

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  daxdqjwaww1gr, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ *   from mytable  where mycolumn is null

Plan hash value: 1816312439

-----------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  INDEX RANGE SCAN| I1   |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MYCOLUMN" IS NULL)

19 rows selected.

Как видно из результата, индекс действительно используется.

С наилучшими пожеланиями, Роб.

Если ваш столбец не содержит NULL, то лучше всего объявить его как NOT NULL, так как оптимизатор может выбрать более эффективный план выполнения запросов.

Однако если в вашем столбце есть NULL, то у вас не так много вариантов (значение по умолчанию, отличное от NULL, может создать больше проблем, чем решить).

Как упоминал Quassnoi, NULL значения в Oracle не индексируются, или, что более точно, строка не будет индексирована, если все индексируемые столбцы равны NULL. Это означает, что:

• NULL значения могут потенциально ускорить поиск, поскольку индекс будет содержать меньше строк.
• Вы все еще можете индексировать строки с NULL, добавив в индекс другой столбец с NOT NULL или даже константу.

Следующий скрипт демонстрирует способ индексирования NULL значений:

CREATE TABLE TEST AS 
SELECT CASE
          WHEN MOD(ROWNUM, 100) != 0 THEN
           object_id
          ELSE
           NULL
       END object_id
  FROM all_objects;

CREATE INDEX idx_null ON test(object_id, 1);

SET AUTOTRACE ON EXPLAIN

SELECT COUNT(*) FROM TEST WHERE object_id IS NULL;

В этом примере создается таблица, содержащая как значения, так и NULL, и далее создается индекс, который включает константное значение, позволяющее индексировать NULL значения.

Nullable поля могут существенно повлиять на производительность при выполнении запросов с использованием "NOT IN". Дело в том, что строки с индексированными полями, установленными в значение NULL, не индексируются в B-Tree индексах, поэтому Oracle вынужден выполнять полное сканирование таблицы, чтобы проверить наличие пустых значений, даже если индекс существует.

Рассмотрим следующий пример:

create table t1 as select rownum rn from all_objects;

create table t2 as select rownum rn from all_objects;

create unique index t1_idx on t1(rn);

create unique index t2_idx on t2(rn);

delete from t2 where rn = 3;

explain plan for
select *
  from t1
 where rn not in ( select rn
                     from t2 );

Результат выполнения explain plan покажет следующее:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 50173 |   636K|  3162   (1)| 00:00:38 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 50205 |   637K|    24   (5)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   | 45404 |   576K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Как видно из плана выполнения, запрос должен проверять наличие значений NULL, и поэтому он выполняет полное сканирование таблицы t2 для каждой строки в таблице t1.

Теперь, если мы сделаем поля не допускающими NULL, запрос сможет воспользоваться индексом:

alter table t1 modify rn not null;

alter table t2 modify rn not null;

explain plan for
select *
  from t1
 where rn not in ( select rn
                     from t2 );

Результат будет следующим:

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  2412 | 62712 |    24   (9)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |        |  2412 | 62712 |    24   (9)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | T1_IDX | 50205 |   637K|    21   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| T2_IDX | 45498 |   577K|     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Теперь запрос выполняется значительно быстрее, так как используется индекс, и полное сканирование больше не требуется. Это подтверждает, что правильная настройка полей в таблицах может значительно повысить производительность запросов в Oracle.