| ORA-12054 while creating materialized view [message #75139] |
Sun, 03 February 2002 22:08  |
Sascha Quint
Messages: 1
Registered: February 2002
|
Junior Member |
|
|
We are trying to create a materialized view with the following statements:
CREATE TABLE agg
(FELD1 NUMBER, FELD2 NUMBER, FELD3 NUMBER, FELD4 NUMBER, d NUMBER);
CREATE MATERIALIZED VIEW LOG ON TESTTABLE WITH ROWID(FELD1, FELD2, FELD3, FELD4) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW TESTMATVIEW
REFRESH FAST ON COMMIT
AS SELECT
feld4,
SUM(FELD2+FELD3),
COUNT(*)
FROM TESTTABLE
GROUP BY FELD4;
and get the error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
the following example from an oracle tutorial is working, but we can't see any difference to our example:
CREATE TABLE agg
(u NUMBER, a NUMBER, b NUMBER, c NUMBER, d NUMBER);
CREATE MATERIALIZED VIEW LOG ON agg
WITH ROWID (u,a,b,c,d)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW sn0
REFRESH FAST ON COMMIT
AS SELECT SUM(b+c), COUNT(*), a, d, COUNT(b+c)
FROM agg
GROUP BY a,d;
What's wrong???
Sascha
|
|
|
|
|
|
|
|
|
|
| Re: ORA-12054 while creating materialized view [message #75222 is a reply to message #75139] |
Tue, 23 July 2002 13:45  |
Juan Calfucura
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
|
To create a refresh-on-commit materialized view (ON COMMIT REFRESH clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege.
|
|
|
|
| Re: ORA-12054 while creating materialized view [message #75587 is a reply to message #75139] |
Fri, 10 December 2004 23:43 |
Jonas Claesson
Messages: 1
Registered: December 2004
|
Junior Member |
|
|
You cant use aggregate functions (AVG, SUM) with the fast refresh option. Fast refresh is used for simple replicas of the master table. Fast refresh updates the materialized view immediately for each change on the master table. It would be a performance killer to recalculate the aggregate functions after each insert, update on the master table.
Use another refresh option :)
Jonas
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
