Как проиндексировать столбец даты с нулевыми значениями?

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

Демонстрация:

Создайте таблицу с 10 000 строками, из которых только 6 содержат значение NULL для столбца a_date.

SQL> create table mytable (id,a_date,filler)
  2  as
  3   select level
  4        , case when level < 9995 then date '1999-12-31' + level end
  5        , lpad('*',1000,'*')
  6     from dual
  7  connect by level <= 10000
  8  /

Table created.

Сначала я покажу, что если вы просто создаете индекс для столбца a_date, индекс не используется, когда вы используете предикат «где a_date имеет значение null»:

SQL> create index i1 on mytable (a_date)
  2  /

Index created.

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

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select id
  2       , a_date
  3    from mytable
  4   where a_date is null
  5  /

        ID A_DATE
---------- -------------------
      9995
      9996
      9997
      9998
      9999
     10000

6 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6 Bytes=72)
   1    0   TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=72 Card=6 Bytes=72)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        720  consistent gets
          0  physical reads
          0  redo size
        285  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

720 согласованных получений и полное сканирование таблицы.

Теперь измените индекс, включив в него константу 1, и повторите тест:

SQL> set autotrace off
SQL> drop index i1
  2  /

Index dropped.

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

Index created.

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

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select id
  2       , a_date
  3    from mytable
  4   where a_date is null
  5  /

        ID A_DATE
---------- -------------------
      9995
      9996
      9997
      9998
      9999
     10000

6 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=6 Bytes=72)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MYTABLE' (Cost=2 Card=6 Bytes=72)
   2    1     INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=2 Card=6)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        285  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

6 согласованных выборок и сканирование диапазона индекса.

С уважением, Роб.

Вы имеете в виду, что ваши запросы будут такими?

select ...
from mytable
where (datecol between :from and :to
       or datecol is null);

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

create index mytable_fbi on mytable (case when datecol is null then 1 end);

Затем измените свой запрос на:

select ...
from mytable
where (datecol between :from and :to
       or case when datecol is null then 1 end = 1);

Вы можете заключить регистр в функцию, чтобы сделать его более приятным:

create or replace function isnull (p_date date) return varchar2
DETERMINISTIC
is
begin
    return case when p_date is null then 'Y' end;
end;
/

create index mytable_fbi on mytable (isnull(datecol));

select ...
from mytable
where (datecol between :from and :to
       or isnull(datecol) = 'Y');

Я убедился, что функция возвращает NULL, если дата не равна нулю, поэтому в индексе сохраняются только нулевые даты. Также мне пришлось объявить функцию как ДЕТЕРМИНИСТИЧЕСКАЯ. (Я изменил его так, чтобы он возвращал «Y» вместо 1 просто потому, что имя «isnull» предполагает, что это должно быть; не стесняйтесь игнорировать мои предпочтения!)