选择最近的行，优化（Oracle SQL）

Question

我很乐意为您提供翻译帮助，以下是您需要翻译的内容：

我需要一些关于以下查询的指导。我们有一个实验列表及其当前进度状态（为简单起见，我已将状态减少到4种，但我们的数据中有10种不同的状态）。我最终需要返回所有未完成实验的当前状态列表。

假设有一个表exp_status，

Experiment | ID     | Status
----------------------------
     A     |   1    | Starting 
     A     |   2    | Working On It
     B     |   3    | Starting
     B     |   4    | Working On It
     B     |   5    | Finished Type I
     C     |   6    | Starting
     D     |   7    | Starting
     D     |   8    | Working On It
     D     |   9    | Finished Type II
     E     |   10   | Starting
     E     |   11   | Working On It
     F     |   12   | Starting 
     G     |   13   | Starting
     H     |   14   | Starting
     H     |   15   | Working On It
     H     |   16   | Finished Type II

期望的结果集：

  Experiment | ID   | Status
----------------------------
     A     |   2    | Working On It
     C     |   6    | Starting
     E     |   11   | Working On It
     F     |   12   | Starting 
     G     |   13   | Starting

最新的ID号将对应于最新的状态。

目前，我拥有的代码执行需要 150 秒。

    SELECT *
    FROM 
          (SELECT Experiment, ID, Status, 
          row_number () over (partition by Experiment
          order by ID desc) as rn
          FROM exp_status)
    WHERE rn = 1
    AND status NOT LIKE ('Finished%')

事实上，这段代码浪费了很多时间。结果集中有45,000行数据，从一个包含3.9百万行的表中提取而来。这是因为大多数实验都处于完成状态。代码会遍历所有实验并对它们进行排序，最后只过滤掉已完成的实验。表中约95%的实验都处于完成阶段。我无法想出如何使查询首先挑选出所有没有“Finished”状态的实验和状态。我尝试了以下方法，但性能非常慢。

SELECT *
FROM exp_status
WHERE experiment NOT IN 
(
  SELECT experiment
  FROM exp_status
  WHERE status LIKE ('Finished%')
)

非常感谢您的帮助！

- User

---

如果状态列被索引，将status not like ...条件移入带有row_number()函数的内部选择中可能是值得尝试的。 - user330315

Answer 1

根据您的需求，我认为您当前使用row_number()的查询是最有效的之一。这个查询需要时间不是因为它必须对数据进行排序，而是因为需要阅读大量的数据（与获取时间相比，额外的cpu时间微不足道）。此外，第一个查询进行了全表扫描，这确实是读取大量数据的最佳方法。

如果您想提高性能，您需要找到一种减少读取行数的方法。第二个查询并没有朝着正确的方向发展：

1. 内部查询将很可能会进行全表扫描，因为“finished”行将分散在整个表中，并且可能代表所有行的很大比例。
2. 外部查询也很可能是一个全表扫描和一个美好的ANTI-HASH JOIN，这应该比45k *（每个实验的状态更改数量）非唯一索引扫描快。

所以第二个查询似乎至少有两倍的读取次数（加上联接）。

如果您真的想提高性能，我认为您需要进行设计更改。

例如，您可以建立一个活动实验表并加入到这个表中。您可以通过材料化视图或修改插入实验状态的代码来维护此表。您还可以进一步存储此表中的最后一个状态。维护这个“最后的状态”可能会增加额外的负担，但这可以通过提高性能来证明。

- Vincent Malgrat

---

谢谢您的建议——我可能会继续使用row_number()方法！ - User

关键在于查询语句中的status not LIKE 'Finished%'部分。在对其进行row_number()操作之前，通过筛选未完成实验来减少所查询的数据集可能会有所帮助。 - Birupakhya Dash

@justCallMeBiru 在内部查询中，您无法知道哪些实验已经完成，哪些没有完成，这就是为什么我们需要外部查询的原因。 - Vincent Malgrat

Answer 2

考虑按 状态 对您的表进行分区

www.orafaq.com/wiki/Partitioning_FAQ

您还可以创建物化视图，以避免在频繁查询这些类型的查询时必须重新计算聚合。

您能否提供查询的执行计划。如果没有这些计划，很难知道它为什么需要这么长时间。

- iggy_pop

---

分区似乎很有趣。虽然我没有执行计划权限，但正如Vincent在上面所说的那样，长时间可能只是由于表格的大尺寸造成的。 - User

Answer 3

你可以稍微改进一下你的第一个查询，使用这个变体：

select experiment
     , max(id) id
     , max(status) keep (dense_rank last order by id) status
  from exp_status
 group by experiment
having max(status) keep (dense_rank last order by id) not like 'Finished%'

如果你比较这些计划，你会发现有一步少了

祝好，
罗布

- Rob van Wijk