时间戳

首页 > 解决方案 > 如何在 SQL 中以高性能的方式使用 PARTITION BY 获取最新记录？

sql - 如何在 SQL 中以高性能的方式使用 PARTITION BY 获取最新记录？

问题描述

在我们用于获取仪表板和报告信息的规范化 Oracle 12.2 数据库中，我们发现用户经常遇到想要了解每个分区的最新记录的数据的需求。在某些情况下，我们可以将数据过滤到较小的子集，而在其他情况下，我们需要整个数据集。在大多数情况下，用户希望同时了解多个分区的最新结果。在 oracle 中，典型的模式如下：

select * from (
    select my_table.*,
           row_number() over (partition by fk1, fk2, ... order by my_date desc) rn
    from my_table
    [where fk1 = 1234]
) where rn = 1

最初，为了方便起见，我们想将其抽象为视图，以便人们可以针对视图编写查询。我们尝试了这样的事情：

create view my_table_latest as (
    select * from (
        select my_table.*,
               row_number() over (partition by fk1, fk2, ... order by my_date desc) rn
        from my_table
    ) where rn = 1
)

select * from my_table_latest where fk1 = 1234

不幸的是，这有两个问题。首先，视图内部的分析函数似乎总是在应用任何过滤器之前对整个表进行计算。因此，无论使用什么过滤器和索引，都会扫描整个表。其次，当用于具有数百万条记录的表时，查询花费的时间比我们想要的要长。

鉴于我们希望我们的数据保持相对新鲜（在 10 分钟内），以高性能方式获取某些业务密钥的最新记录的最佳方法是什么？获取数据的方法应该隐藏在视图中，以便前端仪表板应用程序可以轻松使用它。

以下是我们的两个想法：

标签： sqloracleoracle12cquery-performancesqlperformance

解决方案

您可能会发现使用相关子查询更快：

select t.*
from my_table t
where t.my_date = (select max(t2.my_date)
                   from my_table t2
                   where t2.fk1 = t.fk1 and t2.fk2 = t.fk2 and . . .
                  );

当在外部查询中使用带有过滤功能的视图时，Oracle 可能会发现更容易对此进行优化。出于性能考虑，您需要在(fk1, fk2, . . ., my_date).

这假设日期对于给定的键组合不重复。

推荐阅读