选择最近的行，优化(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万的表中提取了4.5万行。这是因为大多数实验都处于完成状态。代码会遍历并排序所有这些，然后在最后只过滤掉已完成的内容。表中大约95%的实验处于完成阶段。我不知道如何让查询首先挑出所有实验和没有"完成"的状态。我试了以下几项，但表现很慢。

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

任何帮助都将不胜感激！

Answer 1

考虑到您的需求，我认为您当前使用row_number()的查询是最高效的查询之一。这个查询需要时间，不是因为它必须对数据进行排序，而是因为首先有太多数据要读取（与提取时间相比，额外的cpu时间可以忽略不计）。此外，第一个查询生成FULLSCAN，这确实是读取大量数据的最佳方式。

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

1. 内部查询可能是完全扫描，因为"已完成"的行将分布在整个表中，并且可能代表所有行的很大百分比
2. 外部查询也可能是一个完整扫描和一个漂亮的ANTI-HASH JOIN，它应该比45k*（每个实验的状态更改次数）非唯一索引扫描更快

因此，第二个查询的读取次数似乎至少是它的两倍（加上一个联接）。

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

例如，您可以构建一个活动实验表并加入该表。您可以将该表作为物化视图进行维护，也可以对插入实验状态的代码进行修改。您可以更进一步，将最后一个状态存储在此表中。保持这种"最后状态"可能是一种额外的负担，但这可以通过提高性能来证明。

Answer 2

考虑使用status 对表进行分区

www.orafaq.com/wiki/Partitioning_FAQ

您还可以创建具体化视图，以避免在这些类型的查询频繁的情况下重新计算聚合。

您能提供查询的执行计划吗。如果没有这些，很难知道花费这么长时间的确切原因

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%'

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

问候，
抢劫