oracle - 如何索引具有空值的日期列？

Question

当某些行具有空值时，我应该如何索引日期列？
我们必须在日期范围和具有空日期的行之间选择行。

我们使用 Oracle 9.2 及更高版本。

我找到的选项

在日期列上使用位图索引

在日期列上使用索引，在日期为空时值为 1 的状态字段上使用索引

在日期列和其他授予的非空列上使用索引

我对选项的想法是:

to 1:表必须有许多不同的值才能使用位图索引
to 2:我必须仅为此目的添加一个字段，并在我想检索空日期行时更改查询
to 3: 将一个字段添加到一个实际上不需要的索引的锁很棘手

这种情况的最佳做法是什么？
提前致谢

我读过的一些信息:

Oracle Date Index
When does Oracle index null column values?

编辑

我们的表有 300,000 条记录。每天插入和删除 1,000 到 10,000 条记录。 280,000 条记录的 delivery_at 日期为空。它是一种拣选缓冲区。

我们的结构(翻译成英文)是:

create table orders
(
  orderid              VARCHAR2(6) not null,
  customerid           VARCHAR2(6) not null,
  compartment          VARCHAR2(8),
  externalstorage      NUMBER(1) default 0 not null,
  created_at           DATE not null,
  last_update          DATE not null,
  latest_delivery      DATE not null,
  delivered_at         DATE,
  delivery_group       VARCHAR2(9),
  fast_order           NUMBER(1) default 0 not null,
  order_type           NUMBER(1) default 0 not null,
  produkt_group        VARCHAR2(30)
)

Answer 1

除了 Tony 的出色建议之外，还有一个选项可以以无需调整查询的方式为您的列编制索引。诀窍是为您的索引添加一个常量值。

演示:

创建一个包含 10,000 行的表，其中只有 6 行包含 a_date 列的 NULL 值。

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 列上创建一个索引，那么当您使用谓词“where a_date is 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 个一致的获取和索引范围扫描。

问候，
抢。