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

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

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 分钟内(，以高性能方式获取某些业务密钥的最新记录的最佳方法是什么？获取数据的方法应隐藏在视图中，以便前端仪表板应用程序可以轻松使用。

以下是我们的两个想法：

实例化视图- 使用实例化视图每 10 分钟重新计算一次结果。鉴于查询需要几分钟的计算时间，我们担心这可能效果不佳。此外，根据我们在视图中进行分析查询的发现，我们怀疑由于使用了分析函数，使用更有效的刷新策略将不起作用。
边走边跟踪- 鉴于我们可以访问写入数据的代码，并且数据始终是最旧的 ->最新的，我们可以轻松地跟踪最新记录并将其保存到不同的表中。然后，可以创建一个视图，该视图使用此信息并联接回原始表以获取其余记录详细信息。"自定义索引"表类似于(fk1，fk2，my_table.pk，date(。不幸的是，这将需要更改代码。

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

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)上有一个索引。

这假定给定的键组合不会重复日期。

一点头脑风暴：

创建表示行类型的 SQLTYPE;阅读 Oracle 文档了解更多详情

创建类型 my_table_t as(/* 与 my_table */相同的字段(;

创建一个PIPELINED函数，该函数接收您需要的所有参数并返回您需要的行类型。有关更多详细信息，请阅读有关管道表函数的 Oracle 文档。在最通用的形式中，您将收到一个包含用户提供的 SQL 过滤器的varchar2，但由于它可能容易受到注入攻击，我建议使用其他替代方法，例如接受 (fk1， ...， fkn( 作为参数。我们将此函数称为query_my_table。在此查询中，您可以动态生成所需的确切 SQL，打开REF CURSOR并PIPE每一行。在为每种情况生成特定 SQL 时，您可以发出所需的确切查询，而不需要依赖视图行为。

创建或替换函数query_my_table(FK1 编号，...，FKN 编号(返回my_table_t流水线为 查询瓦尔查尔2; 开始  查询 ：=/* 使用您需要的确切 SQL 创建一个字符串 *//* 打开引用光标进行查询 fk1， ...， fkn */ 圈 /* not_found时获取和退出 *//* 将数据加载到my_table_t实例中 */ 管排(my_table_t_instance(; 结束循环;/* 关闭参考光标 */返回; end issue

然后，您可以通过发出以下命令来SELECT：

select * from table(query_my_table(fk1, ..., fkn));

这只是dbms_xplan.display使用的相同功能的另一个应用程序。我能想到的这种方法的主要问题是它不能很好地组合：由于 Oracle 没有关于table(...)位的统计信息，如果您开始将其与其他表连接，优化器将无法优化那么多。但如果它是"最终查询"，它应该可以正常工作。

可以使用以下查询并将其实现为视图：

select *
from my_table
where ROWID IN (SELECT first_value(ROWID) over (PARTITION BY fk1, fk2, ...
ORDER BY my_date DESC)
FROM my_table)

fk1，fk2,...,my_date上的索引可能有助于加快查询速度。

相关内容

最新更新

热门标签：