基于慢函数创建年度报告物化视图

Question

考虑以下情况：

我有一个包含100万个产品ids的表，products：

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

还有一个相对缓慢的函数。

function gerProductMetrics(pid,date) return number

它在给定日期返回给定产品的某个度量。

还有每年执行的基于以下查询的年度报告：

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

在给定的一年中，执行该查询大约需要20-40分钟。

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

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？

每年年底都需要度量数据。

但任何一年的数据都可以在任何时候要求。

注意，我无法控制慢函数--这只是一个给定的函数。谢谢。

Answer 1

首先，您没有"group“查询，因此可以删除该查询。

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

Answer 2

不要创建物化视图。这不仅仅是一个性能问题。这也是一个存档问题:你不想冒历史结果可能改变的风险。

我的建议是创建一个带有“年份”列的表。每年运行一次查询，并将行插入到新表中。这是结果的存档。

注意:如果您想重新计算前几年，因为结果可能已经更改(假设数据是以某种方式更新的)，那么您应该将这些结果存储在一个单独的表中，并决定哪个版本是“正确的”版本。您可能会发现，您需要一个同时具有"as-of“日期和"run- date”的归档表，以查看结果可能发生的变化。