oracle - 如何建模数据/索引以快速找到时间片

Question

我们的数据库中有很多表，其中包含仅在特定时间段内相关/有效的数据。例如契约(Contract)，他们有一个 start_date 和一个 end_date。而且不一定是整月。

现在这是针对此表的典型查询类型:

SELECT
  *
FROM
  contracts c
WHERE
      c.start_date <= :1
  AND c.end_date >= :2
  AND c.region_id = :3

因为我们的表中有 20 年的数据(~7000 天)，所以日期是非常好的过滤条件，尤其是当 :1 和 :2 是同一天时。 region_id 不是一个很好的过滤条件，因为没有那么多(~50)。在这个例子中，我们的表上有(除其他外)2 个索引:

contracts_valid_index (start_date, end_date)
contracts_region (region_id)

不幸的是，上面的查询通常会使用 contracts_region 索引，因为优化器认为它更便宜。这背后的原因很简单:当我在数据中间选择一天时，数据库会认为超过 start_date 的索引并不是很好，因为它只会过滤掉一半的数据。通过查看 end_date 同样适用。所以优化器认为他只能过滤掉我1/4的数据。因为他不知道 start_date 和 end_date 通常非常接近，这个索引会非常有选择性。

使用 contracts_valid_index 的执行计划比使用 contracts_region 的执行计划成本更高。但实际上 contracts_valid_index 要好得多。

我目前认为我无法通过创建更好的索引来加快查询速度(除了删除除 contracts_valid_index 之外的所有索引)。但也许我的数据模型对查询优化器来说不是很好。所以我假设其他人也有类似的需求，并且很想知道他们如何建模他们的数据或优化他们的数据表/索引。

有什么建议吗？

Answer 1

由于您表明您使用的是 Oracle 12c，因此将 Start_Date 和 End_Date 列定义为 temporal 可能会有所帮助有效时间列，前提是它们匹配适当的时间有效性语义(start_date 和 end_date 需要是时间戳，end_date 必须 > start_date 或可能为 null，并且有效时间段包括开始日期但不包括结束日期，即它是部分关闭/打开range 不同于通常的 between 运算符，后者表示完全封闭的范围)。例如:

ALTER TABLE contracts ADD (PERIOD FOR valid_time (start_date, end_date));

然后您可以查询给定有效期的契约(Contract)表:

SELECT 
  c.* 
FROM
  contracts VERSIONS PERIOD FOR valid_time BETWEEN :1 AND :2 c
WHERE
  c.region_id = :3

这在语义上类似于:

SELECT 
  c.* 
FROM
  contracts c
WHERE
      :1 < end_date
  AND start_date <= :2
  AND c.region_id = :3

或者查询在特定时间点而不是时间范围内有效的记录:

SELECT 
  c.* 
FROM
  contracts AS OF PERIOD FOR valid_time :1 c
WHERE
  c.region_id = :2

语义上类似于:

SELECT 
  c.* 
FROM
  contracts c
WHERE
      :1 BETWEEN start_date AND end_date
  and :1 <> end_date
  and c.region_id = :2

我不确定 start_date 和 end_date 的空值是否分别表示时间的开始和结束，因为我目前没有要测试的 R12 实例。