logo标签列表

如何为具有空值的日期列建立索引？

oracleoptimizationindexingnull
13
我应该如何为一个包含空值的日期列建立索引？ 我们需要选择某个日期范围内的行以及日期为空的行。
我们使用Oracle 9.2及更高版本。
我找到的选项有：
  1. 在日期列上使用位图索引
  2. 在日期列和一个状态字段上分别建立索引，当日期为空时该字段的值为1
  3. 在日期列和另一个已授予非空属性的列上分别建立索引
我的想法是：
对于1：表中有太多不同的值，无法使用位图索引
对于2：我必须为此目的添加一个字段，并在检索空日期行时更改查询
对于3：向索引添加一个不是真正所需的字段会很棘手
这种情况下最佳实践是什么？ 提前感谢您的回答。
我读到的一些信息： Oracle日期索引 Oracle何时索引空列值？

编辑

我们的表有300,000条记录。每天插入和删除1,000到10,000条记录。280,000条记录的delivered_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)
)
1
你好，一行占用多少字节？有多少行/增长率是多少？ - Alexander Malakhov
3
如果300,000行中有280,000行包含null值且您想选择它们全部，为什么要对null值建立索引呢？如果您打算阅读整本书，为什么要通过索引来阅读一本书呢？ - Rob van Wijk
@Alexander：你的问题很重要。正是因为它，我分析了数据，这本来是我应该优先做的。 - Heinz Z.
4个回答
15

除了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次一致性获取和一个索引范围扫描。

敬礼， 罗布。

1
B-tree索引条目仅包含非空值。因此，当您仅在a_date列上拥有索引时，该索引包含9994个条目。添加常量使所有10,000行出现在索引中，从而适用于查询。 - Rob van Wijk
12

我们的表格有300,000条记录.... 其中280,000条记录缺少delivered_at日期。

换句话说，几乎整个表格都满足查询DELIVERED_AT为空的条件。使用索引完全不适用于这种查询。最好的方法是进行全表扫描。

如果您有企业版许可证，并且有多余的CPU资源，使用并行查询可以减少运行时间。

毕竟你是绝对正确的（就像Rob在他的评论中所说）。我会投赞成票给其他答案。它们可以在其他情况下提供帮助，而且一开始没有关于数据的信息。 - Heinz Z.
@HeinzZ - 非常相关的一点。数据库都是关于数据的。所以，当涉及到性能调整、卷和分布 - 尤其是歪斜分布 - 这些关键信息非常重要。 - APC
9
你的意思是你的查询会像这样吗？
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，以便仅存储空日期在索引中。此外，我必须将该函数声明为DETERMINISTIC。(我将其更改为返回“Y”而不是1仅仅是因为在我看来，“isnull”这个名称表明它应该这样做；您可以忽略我的喜好！)

0
避免表格查找，像这样创建索引：
create index i1 on mytable (a_date,id) ;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接