ORA-12054:无法为物化视图设置ON COMMIT刷新属性

Question

我在为聚合创建物化视图时遇到了一个问题。如果源表上出现DML，则物化视图应该自动刷新并显示更新后的结果。这个想法是存储聚合结果并直接获取数字，而不是进行查询，我基本上想看看这是否符合我们的目标。我们的表每天将有多达300万次插入。

例8-3例3:创建物化视图我做了:

CREATE MATERIALIZED VIEW LOG ON table WITH SEQUENCE, ROWID
(SUBJECTID)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW ih_data_aggregated_view
PARALLEL 
BUILD IMMEDIATE 
REFRESH FAST ON COMMIT
AS
SELECT SUBJECTID ,count(*)as totalcount ,avg(price)as avgprice,sum(price) as totalprice
FROM table  
WHERE SUBJECTID='xxxxx'  GROUP by SUBJECTID;

但是这得到:

java.sql。SQLException: sqllexception: ORA-12054:无法为物化视图设置ON COMMIT刷新属性

Answer 1

文档包含了对物化视图和带有聚合的物化视图的一般限制。

虽然处理这些列表是有指导意义的，但您可以通过检查dbms_mview.explain_mview过程的结果来查看是否可以快速刷新现有或潜在的物化视图:

set serveroutput on
declare
  msg_array SYS.ExplainMVArrayType;
begin
  dbms_mview.explain_mview (q'[
SELECT SUBJECTID ,count(*)as totalcount ,avg(price)as avgprice,sum(price) as totalprice
FROM your_table
WHERE SUBJECTID='xxxxx'
GROUP by SUBJECTID
]',
     msg_array);
   for i in msg_array.first..msg_array.last loop
     dbms_output.put_line(rpad(msg_array(i).capability_name, 30)
       ||' '|| msg_array(i).possible
       ||' '|| msg_array(i).msgtxt);
  end loop;
end;
/
...
REFRESH_FAST                   F 
...
REFRESH_FAST_AFTER_INSERT      F agg(expr) requires correspondng COUNT(expr) function
REFRESH_FAST_AFTER_ONETAB_DML  F SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML  F see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     F see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               F PCT is not possible on any of the detail tables in the materialized view
...

正如@vercelli所提到的，以及REFRESH_FAST_AFTER_INSERT消息所建议的，您需要将count(*)更改为count(price)。但这并不是故事的全部;如果你只是改变它，你会看到:

REFRESH_FAST                   F 
...
REFRESH_FAST_AFTER_INSERT      F mv log does not have all necessary columns
REFRESH_FAST_AFTER_ONETAB_DML  F see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML  F COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ANY_DML     F see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

您的物化视图日志必须包含您正在聚合的列:

CREATE MATERIALIZED VIEW LOG ON your_table WITH SEQUENCE, ROWID
(SUBJECTID,PRICE)
INCLUDING NEW VALUES;
Materialized view LOG created.
CREATE MATERIALIZED VIEW ih_data_aggregated_view
PARALLEL 
BUILD IMMEDIATE 
REFRESH FAST ON COMMIT
AS
SELECT SUBJECTID ,count(price)as totalcount ,avg(price)as avgprice,sum(price) as totalprice
FROM your_table
WHERE SUBJECTID='xxxxx'
GROUP by SUBJECTID;
Materialized view IH_DATA_AGGREGATED_VIEW created.

缺失的count(*)仍然会被报告，但由于这是针对单个表的，因此不会阻止快速刷新。值得注意的是，如果你的price列是空的，那么count(price)和count(*)可能会给出不同的结果;如果是这种情况，您可能希望将这两个计数作为MV中的列。