gpt4 book ai didi

Oracle 物化 View

转载 作者:行者123 更新时间:2023-12-03 03:29:06 27 4
gpt4 key购买 nike

根据oracle文档,我们不能使用快速刷新方法来刷新聚合物化 View 。我在Oracle文档中找到了这个例子: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm

CREATE MATERIALIZED VIEW LOG ON times
WITH ROWID, SEQUENCE (time_id, calendar_year)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON products
WITH ROWID, SEQUENCE (prod_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;

每次我尝试使用聚合和快速刷新时,都会出现错误。
快速刷新和聚合功能配合使用有什么特别的技巧吗?

亲切的问候

最佳答案

根据我的调查,使用聚合函数和快速刷新方法创建MV,您的MV并且MV日志应该有特殊的结构,以便查看MV的正确结构以及在脚本下面运行的MV日志:

begin
dbms_advisor.tune_mview(task_name=>:t,
mv_create_stmt=>'create materialized view mv1 refresh fast
as select job,sum(sal) from emp group by job');
end;

然后执行以下查询以查看所需的 MV 结构和 MV 日志:

select  dbms_lob.substr( statement, 4000, 1 ), statement from user_tune_mview
where task_name='TASK_2042' order by action_id;

关于Oracle 物化 View ,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25720486/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com