A 库: 有表A,并且建有物化日志 。
B 库希望建立一个A 表的物化视图。
但在建立物化视图时报错 :
ORA-12054:cannot set the on commit refresh attribute for the materialized view物化视图建立SQL如下:
create materialized view A
refresh fast
on commit
as
select * from A@DBLINK
请教大侠!!!
B 库希望建立一个A 表的物化视图。
但在建立物化视图时报错 :
ORA-12054:cannot set the on commit refresh attribute for the materialized view物化视图建立SQL如下:
create materialized view A
refresh fast
on commit
as
select * from A@DBLINK
请教大侠!!!
解决方案 »
- 一个删除语句的问题
- |zyciis| Update整个表3小时没反应,改为分段更新 为什么一条Update语句,越执行越慢呢?
- 求助,关于functions
- 为了删除表空间,我直接删除了一个".dbf"文件,结果数据库起不来了,怎么办?
- 超出表空间'USERS'的空间限量,无法创建表
- 急问,我把一台机子的Oracle客户端81升到92,升级会不会出现问题?
- 初学者问题,高手帮我详细答一下
- 数据库系统表有坏块,如何修复呀??走过路过的帮忙楚楚主意,谢谢先。
- 关于Oracle的用户数问题,请指教!
- Oracle对硬件的要求是否很高,需要什么最低配置?
- 急,怎么在视图和存储过程,动态创建一个临时视图并返回一个结果集?
- Oracle 转换SQL(start with)
Fast refresh is supported for both ON COMMIT and ON DEMAND materialized views,
however the following restrictions apply:
n All tables in the materialized view must have materialized view logs, and the
materialized view logs must:
n Contain all columns from the table referenced in the materialized view.
n Specify with ROWID and INCLUDING NEW VALUES.
n Specify the SEQUENCE clause if the table is expected to have a mix of
inserts/direct-loads, deletes, and updates.
n Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast
refresh.
n COUNT(*) must be specified.
n For each aggregate AGG(expr), the corresponding COUNT(expr) must be
present.
n 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 Table 8–1 on page 8-15 for further details.
n The SELECT list must contain all GROUP BY columns.
n If the materialized view has one of the following, then fast refresh is supported
only on conventional DML inserts and direct loads.
n Materialized views with MIN or MAX aggregates
n Materialized views which have SUM(expr) but no COUNT(expr)
n Materialized views without COUNT(*)
Such a materialized view is called an insert-only materialized view.
n The COMPATIBILITY parameter must be set to 9.0 if the materialized aggregate
view has inline views, outer joins, self joins or grouping sets and FAST
REFRESH is specified during creation. Note that all other requirements for fast
refresh specified previously must also be satisfied.
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, refer to the Oracle9i Database Performance Tuning
Guide and Reference.
n If there are no outer joins, you may have arbitrary selections and joins in the
WHERE clause.
n 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.
n For materialized views with CUBE, ROLLUP, Grouping Sets, or concatenation of
them, the following restrictions apply:
n 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.
n 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)".
难道通过DBLINK就不能够实现快速刷新吗?
那这样的话Oracle物化视图的功效不是打了折扣吗?
不知道那位大侠做过这方面的东东,给指点下下。