sql - 在非常大的表中选择最近的值

Question

我是一名运维人员，负责从一个非常大的表中提取数据。我不是 DBA，无法对其进行分区或更改索引。表有近 10 亿个条目，没有分区，并且可能被索引“更好”。我需要两个字段，我们将其称为 mod_date 和 obj_id(mod_date 已编入索引)。编辑:我还为“客户”添加了一个过滤器，我在解释计划的屏幕截图中已经模糊了它。

我的资料:
在将近 10 亿行的组中，我们有不到 10,000 个 obj_id 值要在几年内查询(有些甚至可能是 NULL)。一些 <10k obj_ids——可能在 1,000-2,500 之间——每个都有超过 1000 万个 mod_date 值。当 obj_id 超过几百万个 mod_date 时，每个 obj_id 需要几分钟时间使用 MAX(mod_date) 进行扫描和排序。完整的结果集需要超过 12 个小时的时间来查询，并且没有人在没有一些“问题”(锁定、不插电的笔记本电脑等)的情况下完成它。即使我们返回了前 50 行，我们仍然需要导出到 Excel ......它只有大约 8,000 行和 2 列，但我们永远无法完成。

所以这是一个简化的查询，如果它是一个小表，我会使用:

select MAX(trunc(mod_date,'dd')) as last_modified_date, obj_id
from my_table
where client = 'client_name'
and obj_type_id = 12
group by obj_id;

基数为 317917582，“成本”为 12783449



问题:
问题是在给定当前索引的情况下，使用如此大的未分区表进行查询的速度。我见过的关于“最近日期”的所有其他答案都倾向于使用 MAX，可能与 FIRST_VALUE 结合使用，这似乎需要对所有行进行全面扫描才能对它们进行排序，然后确定哪一个是最近的。

我希望有办法避免这种情况，加快结果。似乎 Oracle(我正在使用 Oracle SQL 开发人员)应该能够 取一个 obj_id，从“现在”开始查找最近的 mod_date 行，然后向后工作，一旦找到任何 mod_date 值就继续前进……因为它是一个日期。有没有办法做到这一点？

即使有这么大的表，少于 10,000 个 mod_dates 的 obj_ids 也可以很快(几秒或更短)返回 MAX(mod_date)。我们遇到的问题是拥有最多 mod_date(超过 1000 万)的 obj_id 需要最长的扫描和排序时间，如果我能让 Oracle 首先开始查看最近的，它们“应该”是最快的……因为它会快速找到最近的约会对象并继续前进!

Answer 1

首先，我会说这是一个常见的误解，即为了使查询运行得更快，您需要一个索引(或更好的索引)。当您提取超过 10% 的数据时，全表扫描是有意义的(粗略估计，取决于多块读取计数、块大小等)。

我的建议是设置一个物化 View (MY_MV 或其他)，它只是按查询(跨所有 id)执行分组。如果您需要将 id 限制为 10k 子集，只需确保完全扫描表(检查解释计划)。如果需要，您可以添加完整提示(选择/*+ full(t) */.. from big_table t ...)

然后做:

dbms_mview.refresh('MY_MV','C',atomic_refresh=>false);

就是这样。客户端只返回前 x 行没有问题，当您提取所有内容时，它会重新运行整个查询(呃)。在 long opts 中也更容易跟踪完整扫描(例如，如果您在索引上执行嵌套循环，则更难判断您取得了什么进展)。

完成后，将整个 MV 表转储到文件或任何您需要的文件中。