sql - 选择最近的行，优化 (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%')

问题是，这段代码浪费了时间。结果集是从 390 万个表中提取的 45,000 行。这是因为大多数实验都处于完成状态。代码会遍历并对所有这些进行排序，然后只过滤掉最后完成的。表中大约 95% 的实验处于完成阶段。我不知道如何使查询首先挑选出该实验没有“完成”的所有实验和状态。我尝试了以下方法，但性能非常慢。

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

如有任何帮助，我们将不胜感激!

Answer 1

鉴于您的要求，我认为您当前使用 row_number() 的查询是最有效的查询之一。这个查询花费时间并不是因为它必须对数据进行排序，而是因为首先需要读取大量数据(与获取时间相比，额外的 cpu 时间可以忽略不计)。此外，第一个查询进行完整扫描，这确实是读取大量数据的最佳方式。

如果您想提高性能，您需要找到一种方法来读取更少的行。第二个查询的方向不正确:

1. 内部查询可能是完整扫描，因为“完成”的行将分布在整个表中，并且可能占所有行的很大一部分。
2. 外部查询也可能是完整扫描和良好的 ANTI-HASH JOIN，这应该比 45k *(每个实验的状态更改数量)非唯一索引扫描更快。

因此第二个查询的读取次数似乎至少是两倍(加上联接)。

如果你想真正提高性能，我认为你需要改变设计。

例如，您可以构建一个事件实验表并加入到该表中。您可以将该表维护为物化 View ，或者修改插入实验状态的代码。您可以进一步将最后的状态存储在此表中。维持这种“最后的状态”可能会成为额外的负担，但这可以通过性能的提高来证明。