基于慢函数创建年报物化视图 [英] creating materialized view for annual report based on slow function

问题描述

考虑以下场景:

我有一个包含 100 万个产品 ID 的表 products :

I have a table with 1 million product ids products :

create table products (
pid number,
p_description varchar2(200)
)

还有一个比较慢的功能

function gerProductMetrics(pid,date) 返回数字

返回给定日期给定产品的一些指标.

which returns some metric for the given product at given date.

还有一个每年执行的年度报告，它基于以下查询:

there is also an annual report executed every year that is based on the following query:

select pid,p_description,getProductMetrics(pid,'2019-12-31') from
products

该查询在给定年份执行大约需要 20-40 分钟.

that query takes about 20-40 minutes to execute for a given year.

使用以下方法为此场景创建物化视图 (MV) 是正确的方法吗

would it be correct approach to create Materialized View (MV) for this scenario using the following

CREATE TABLE mydates
(
    mydate     date
 
);

INSERT INTO  mydates (mydate)
     VALUES (DATE '2019-12-31');

INSERT INTO  mydates (mydate)
     VALUES (DATE '2018-12-31');

INSERT INTO  mydates (mydate)
     VALUES (DATE '2017-12-31');


CREATE MATERIALIZED VIEW metrics_summary
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
AS
      SELECT pid,
             getProductMetrics(pid,mydate      AS annual_metric,
             mydate
        FROM products,mydates
   

或者需要永远?

此外，我将如何以及多久更新一次此 MV?

Also, how and how often would I update this MV?

需要每年年底的指标数据.

Metrics data is required for the end of each year.

但可以随时请求任何年份的数据.

But any year's data could be requested at any time.

请注意，我无法控制慢速功能 - 这只是一个给定的.谢谢.

Note, that I have no control over the slow function - it's just a given. thanks.

推荐答案

首先，您没有group by"查询，因此您可以删除它.

First, you do not have a "group by" query, so you can remove that.

如果您需要重新计算所有年份的所有数据，那么 MV 将是最有用的.由于这似乎是一个摘要，无需重新处理旧数据，仅在超过某些阈值日期(如年终)时更新，我建议将结果放在普通表中，并且仅在阈值日期时添加更新使用存储过程(每年?)发生一次.否则，您的 MV 将需要更长时间才能运行，并且每次添加新日期的执行都需要更多系统资源.

An MV would be most useful if you needed to recompute all of the data for all years. As this appears to be a summary, with no need to reprocess old data, updated only when certain threshold dates like end of year are passed, I would recommend putting the results in a normal table and only adding the updates as often as your threshold dates occur (annually?) using a stored procedure. Otherwise your MV will take longer to run and require more system resources with every execution that adds a new date.

这篇关于基于慢函数创建年报物化视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！