| oracle won't allow fast refresh materialized view with union [message #182728] |
Mon, 17 July 2006 18:19  |
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  |
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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
