Home » Server Options » Replication » oracle won't allow fast refresh materialized view with union
oracle won't allow fast refresh materialized view with union [message #182728] Mon, 17 July 2006 18:19 Go to next message
rhaertel80
Messages: 2
Registered: July 2006
Junior Member
We have just begun using materialized views in our organization. I have run into a problem where the "UNION" operator seems to create a complex query from two otherwise non-complex queries. I seem to be following all of the restrictions that I have been able to find. Here's the query that breaks:

DROP MATERIALIZED VIEW ExamineeEventHistory;
CREATE MATERIALIZED VIEW ExamineeEventHistory
PARALLEL BUILD IMMEDIATE
REFRESH FAST
NEXT TRUNC(SYSDATE + 1)
WITH ROWID
AS
SELECT *
FROM TALL.PropertyExpression@serverbeach
WHERE PropertyTextID = -373 AND ObjectTypeID = -17

UNION

SELECT *
FROM TALL.PropertyExpression@serverbeach
WHERE PropertyTextID = -375 AND ObjectTypeID = -17

ERROR at line 14:
ORA-12015: cannot create a fast refresh materialized view from a complex query

Notice that I can create a fast-refreshable materialized view for each of the above select statements individually without any problem whatsoever. In fact, I can use an "OR" statement and it works, but this is a simplified "Debug" version of the query and in the final version I probably won't be able to use an "OR".

Any thoughts on why the UNION is causing this query to be complex?

Thanks in advance!
Re: oracle won't allow fast refresh materialized view with union [message #183657 is a reply to message #182728] Fri, 21 July 2006 15:38 Go to previous message
rhaertel80
Messages: 2
Registered: July 2006
Junior Member
I'm still not sure why the query doesn't work, but there are two solutions. (1) Is to use WITH PRIMARY KEY instead of WITH ROWID (and this requires that I select out a bunch of columns I don't want or need). (2) I can use UNION ALL instead; this requires that I select out a unique identifier in each UNION ALL statement, e.g. SELECT 1 as mytype ... UNION ALL Select 2 as mytype.

Hope this can serve to someone else!
Previous Topic: when config my adv replication,met an error,pls see and help me
Next Topic: Basic Replication Setup - HOW?
Goto Forum:
  


Current Time: Thu Mar 28 12:22:44 CDT 2024