在Oracle中使用快速刷新而不是完整的实例化视图不起作用 [英] Materialized view in oracle with Fast Refresh instead of complete dosn't work

问题描述

我已经创建了一个这样的物化视图，并具有完整的刷新，并且效果很好:

I have created a materialized view with Refresh complete like that and it works well:

CREATE MATERIALIZED VIEW VM4 
   Build immediate 
   refresh complete on commit 
AS 
select C.codecomp, 
    count(c.numpolice) as NbContrat, 
    SUM(c.montant) as MontantGlobal 
from contrat C  
group by c.codecomp;

现在，我想创建一个类似的视图，但是使用快速刷新"，但是它不起作用，它向我显示此错误: 错误

Now I would like to create a similar view but with Refresh Fast but it dosn't work and it shows me this error: error

知道我已经创建了Contrat表的日志，即:

Knowing that I have already created the LOG of the Contrat table ilke that:

CREATE MATERIALIZED VIEW LOG ON contrat with rowid ;

推荐答案

检查

快速刷新的一般限制

实例化视图的定义查询受到如下限制:

The defining query of the materialized view is restricted as follows:

• 实例化视图不得包含对非重复表达式(如SYSDATE和ROWNUM)的引用.

• The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.

实例化视图不得包含对RAW或LONG RAW数据类型的引用.

The materialized view must not contain references to RAW or LONG RAW data types.

它不能包含SELECT列表子查询.

It cannot contain a SELECT list subquery.

在SELECT子句中不能包含分析函数(例如RANK).

It cannot contain analytic functions (for example, RANK) in the SELECT clause.

它不能引用在其上定义了XMLIndex索引的表.

It cannot reference a table on which an XMLIndex index is defined.

它不能包含MODEL子句.

It cannot contain a MODEL clause.

它不能包含带有子查询的HAVING子句.

It cannot contain a HAVING clause with a subquery.

它不能包含具有ANY，ALL或NOT EXISTS的嵌套查询.

It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

它不能包含[START WITH…] CONNECT BY子句.

It cannot contain a [START WITH …] CONNECT BY clause.

它不能包含位于不同站点的多个明细表.

It cannot contain multiple detail tables at different sites.

ON COMMIT实例化视图不能具有远程明细表.

ON COMMIT materialized views cannot have remote detail tables.

嵌套的实例化视图必须具有联接或聚集.

Nested materialized views must have a join or aggregate.

带有GROUP BY子句的物化联接视图和物化聚合视图不能从索引组织的表中选择.

Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.

具有聚合的物化视图的快速刷新限制

定义具有聚集或联接的物化视图的查询具有 快速刷新有以下限制:

Defining queries for materialized views with aggregates or joins have the following restrictions on fast refresh:

• 快速刷新的一般限制"中的所有限制.

ON COMMIT和ON DEMAND均支持快速刷新 实例化视图，但是存在以下限制:

Fast refresh is supported for both ON COMMIT and ON DEMAND materialized views, however the following restrictions apply:

• 实例化视图中的所有表都必须具有实例化视图日志，并且实例化视图日志必须:

• All tables in the materialized view must have materialized view logs, and the materialized view logs must:

• 包含实例化视图中引用的表中的所有列.

• Contain all columns from the table referenced in the materialized view.

• 使用ROWID指定并包含新值.

• Specify with ROWID and INCLUDING NEW VALUES.

如果希望表包含插入/直接加载，删除和更新的组合，请指定SEQUENCE子句.

Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.

仅支持SUM，COUNT，AVG，STDDEV，VARIANCE，MIN和MAX进行快速刷新.

Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.

COUNT(*).

聚合函数必须仅出现在表达式的最外部.也就是说，诸如AVG(AVG(x))或AVG(x)+ AVG(x)之类的集合 不允许.

Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.

对于每个聚合(例如AVG(expr))，必须存在相应的COUNT(expr). Oracle建议将SUM(expr) 指定的.请参阅将物化视图与 汇总以获取更多详细信息.

For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that SUM(expr) be specified. See Requirements for Using Materialized Views with Aggregates for further details.

如果指定了VARIANCE(expr)或STDDEV(expr)，则必须指定COUNT(expr)和SUM(expr). Oracle建议将SUM(expr * expr) 指定的.请参阅将物化视图与 汇总以获取更多详细信息.

If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified. See Requirements for Using Materialized Views with Aggregates for further details.

定义查询中的SELECT列不能是包含来自多个基本表的列的复杂表达式.一个可能 解决方法是使用嵌套的物化视图.

The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.

SELECT列表必须包含所有GROUP BY列.

The SELECT list must contain all GROUP BY columns.

物化视图不是基于一个或多个远程表.

The materialized view is not based on one or more remote tables.

如果您在物化视图日志的过滤器列中使用CHAR数据类型，则主站点的字符集和 物化视图必须相同.

If you use a CHAR data type in the filter columns of a materialized view log, the character sets of the master site and the materialized view must be the same.

如果实例化视图具有以下内容之一，则仅常规DML插入和直接支持快速刷新 加载.

If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.

• 具有MIN或MAX聚合的材料化视图

• Materialized views with MIN or MAX aggregates

具有SUM(expr)但没有COUNT(expr)的材料化视图

Materialized views which have SUM(expr) but no COUNT(expr)

没有COUNT(*)的材料化视图

Materialized views without COUNT(*)

这种物化视图称为仅插入物化视图.

Such a materialized view is called an insert-only materialized view.

具有MAX或MIN的实例化视图如果没有WHERE子句，则可以在删除或混合DML语句后快速刷新.

A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.

删除或混合DML后的最大/最小快速刷新与仅插入情况下的行为不同.它删除并重新计算 受影响组的最大值/最小值.您需要注意 其性能影响.

The max/min fast refresh after delete or mixed DML does not have the same behavior as the insert-only case. It deletes and recomputes the max/min values for the affected groups. You need to be aware of its performance impact.

在FROM子句中具有命名视图或子查询的材料化视图可以快速刷新，前提是这些视图可以完全 合并.有关将合并哪些视图的信息，请参见Oracle数据库. SQL调整指南.

Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged. For information on which views will merge, see Oracle Database SQL Tuning Guide.

如果没有外部联接，则可以在WHERE子句中进行任意选择和联接.

If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.

具有常规外部视图的材料化聚合视图在常规DML和直接加载后可以快速刷新，前提是仅 外部表已被修改.另外，必须存在唯一的约束 内部联接表的联接列.如果有外部联接， 所有联接必须通过AND连接，并且必须使用等于(=) 运算符.

Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Also, unique constraints must exist on the join columns of the inner join table. If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

对于具有CUBE，ROLLUP，分组集或它们的串联的实例化视图，适用以下限制:

For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the following restrictions apply:

• SELECT列表应包含分组区分符，它可以是所有GROUP BY表达式上的GROUPING_ID函数，也可以是 GROUPING对每个GROUP BY表达式使用一个函数.例如，如果 实例化视图的GROUP BY子句为"GROUP BY CUBE(a，b)"， 那么SELECT列表应包含"GROUPING_ID(a，b)"或 组(a)和组(b)"使物化视图更快 可刷新的.

• The SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.

GROUP BY不应导致任何重复的分组.例如，"GROUP BY a，ROLLUP(a，b)"不能快速刷新，因为它 导致重复的分组为(a)，(a，b)和AND(a)".

GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".

我想你错过了

• 必须指定COUNT(*).
• 对于每个聚合(例如AVG(expr))，必须存在相应的COUNT(expr).

• 用ROWID指定并包含新值.

• COUNT(*) must be specified.
• For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present.

• Specify with ROWID and INCLUDING NEW VALUES.

如果希望表包含插入/直接加载，删除和更新的组合，请指定SEQUENCE子句.

Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.

这篇关于在Oracle中使用快速刷新而不是完整的实例化视图不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！