Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view ON COMMIT 12054 error - but it almost works!
Materialized view ON COMMIT 12054 error - but it almost works! [message #239683] Wed, 23 May 2007 01:25 Go to next message
smora
Messages: 59
Registered: May 2006
Member
Hi

Im trying to create a mat. view on a join of two tables using the ON COMMIT attribute, and it works for this case:

CREATE MATERIALIZED VIEW banners_mv
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
WITH PRIMARY KEY
AS
SELECT bb.banner_id, cc.camp_name from banners bb, campaigns cc
where bb.camp_id = cc.camp_id
and bb.banner_status = 1
and cc.camp_status = 1
and bb.banner_name not like '%#%';

However, if i add one more condition (bb.banner_startdate <= sysdate), it gives an error. Any ideas why this one additional clause is causing the error? Can i not use dates in the select?

CREATE MATERIALIZED VIEW banners_mv
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
WITH PRIMARY KEY
AS
SELECT bb.banner_id, cc.camp_name from banners bb, campaigns cc
where bb.camp_id = cc.camp_id
and bb.banner_status = 1
and cc.camp_status = 1
and bb.banner_name not like '%#%'
and bb.banner_startdate <= sysdate;

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Any ideas will be appreciated.
Thanks
SM
Re: Materialized view ON COMMIT 12054 error - but it almost works! [message #239697 is a reply to message #239683] Wed, 23 May 2007 02:26 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

What about trying
refresh fast on commit

What's your Oracle Version ?
Re: Materialized view ON COMMIT 12054 error - but it almost works! [message #239700 is a reply to message #239683] Wed, 23 May 2007 02:31 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Tried
REFRESH FAST
ON COMMIT

Same error. Im using 10.2.0.1.0 EE.
Re: Materialized view ON COMMIT 12054 error - but it almost works! [message #239701 is a reply to message #239700] Wed, 23 May 2007 02:33 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:

Materialized View Restrictions

You should keep in mind the following restrictions:

* The defining query of the materialized view cannot contain any non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).
* The query cannot contain any references to RAW or LONG RAW datatypes or object REFs.
* If the defining query of the materialized view contains set operators (UNION, MINUS, and so on), rewrite will use them for full text match rewrite only.
* If the materialized view was registered as PREBUILT, the precision of the columns must agree with the precision of the corresponding SELECT expressions unless overridden by the WITH REDUCED PRECISION clause.
* If the materialized view contains the same table more than once, it is possible to do a general rewrite, provided the query has the same aliases for the duplicate tables as the materialized view.

General Query Rewrite Restrictions

You should keep in mind the following restrictions:

* If a query has both local and remote tables, only local tables will be considered for potential rewrite.
* Neither the detail tables nor the materialized view can be owned by SYS.
* SELECT and GROUP BY lists, if present, must be the same in the query of the materialized view.
* 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.
* CONNECT BY clauses are not allowed.




Read the Oracle Data Warehousing Guide for your version

[Updated on: Wed, 23 May 2007 02:35]

Report message to a moderator

Re: Materialized view ON COMMIT 12054 error - but it almost works! [message #239707 is a reply to message #239683] Wed, 23 May 2007 02:45 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
I am able to create the MView with SYSDATE in the defining query, provided I use a refresh interval (defined by NEXT). It's only when I try to use ON COMMIT instead of NEXT that I get the error.

SQL>CREATE MATERIALIZED VIEW banners_mv
2 BUILD IMMEDIATE
3 REFRESH FORCE
4 NEXT (sysdate + 1/1440)
5 WITH PRIMARY KEY
6 AS
7 SELECT bb.* from banners bb, campaigns cc
8 where bb.banner_status = 1 and
9 cc.camp_status = 1 and
10 bb.banner_name not like '%#%' and
11 bb.banner_startdate <= sysdate;

Materialized view created.

Elapsed: 00:00:07.65
SQL>

I am surprised and confused that the text u quoted says that we cannot use SYSDATE in the defining query, as i clearly am able to use it here.

Materialized View Restrictions

You should keep in mind the following restrictions:

* The defining query of the materialized view cannot contain any non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).
Re: Materialized view ON COMMIT 12054 error - but it almost works! [message #239730 is a reply to message #239707] Wed, 23 May 2007 03:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
But not if you specified REFRESH FAST.

ON COMMIT requires FAST refresh. The fact that you specified FORCE is irrelevant, when it refreshes ON COMMIT, it must use FAST.

When you specify scheduled FORCE refresh, Oracle will actually use a COMPLETE refresh because FAST is not compatible with SYSDATE.

You can run the MV through DBMS_MVIEW.EXPLAIN_MVIEW - it will tell you that FAST REFRESH is not supported.

Ross Leishman
Re: Materialized view ON COMMIT 12054 error - but it almost works! [message #239733 is a reply to message #239683] Wed, 23 May 2007 03:54 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:
The defining query of the materialized view cannot contain any non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).


Sorry if it confused you..
That particular text(oracle documentation) refers to if you create your MVIEW with enable query rewrite

 CREATE MATERIALIZED VIEW banners_mv
 BUILD IMMEDIATE
 REFRESH FORCE
 NEXT (sysdate + 1/1440)
 WITH PRIMARY KEY
 enable query rewrite
 as ...


It will fail since sysdate "gets" a diffrent value each time(non-repeatable)
Re: Materialized view ON COMMIT 12054 error - but it almost works! [message #239785 is a reply to message #239683] Wed, 23 May 2007 05:19 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
So if I want to use ON COMMIT, I cannot have SYSDATE in the defining query. Are there any popular workarounds to this problem, it seems like a fairly common thing to want?

Can I use either of the following workarounds:
a) A system level trigger that would allow me to create the materialized view within a trigger. The trigger would be fired in response to insert/update on the base table.

b) Pragma Autonomous Transaction to create the materialized view inside a trigger. The trigger would be fired in response to insert/update on the base table.

If either method is possible, is it advisable?

Thank you for your input.
SM
Re: Materialized view ON COMMIT 12054 error - but it almost works! [message #240103 is a reply to message #239785] Thu, 24 May 2007 02:35 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why don't you remove the condition from the MV query so that all rows are propagated to the MV, and then create a VIEW over the MV that includes the filter.

Ross Leishman
Re: Materialized view ON COMMIT 12054 error - but it almost works! [message #240495 is a reply to message #239683] Fri, 25 May 2007 01:16 Go to previous message
smora
Messages: 59
Registered: May 2006
Member
Yeah that would have worked...

I created a procedure that runs every night to update certain fields in the base table based on the date condition. This way i dont need to include the date condition in the MV, and can use ON COMMIT.

Thanks to everyone who replied.
SM
Previous Topic: Upload program, for uuploading a csv file
Next Topic: i'm lazy - here's my pathetic title
Goto Forum:
  


Current Time: Fri Dec 02 23:07:21 CST 2016

Total time taken to generate the page: 0.08427 seconds