Materialized view returning different results

Hi everyone,

I have a very complex query that I am trying to optimize. Part of that query joins to two different views that pull data from a very large table (18000+ records). I'm not a dba but one suggested that I use a materialized view instead of a regular one especially since the data in question isn't likely to change very often.

I created two materialized views with the exact same query used to create the two older views. The following code was used to create them, minus the query part. I used both primary key and rowid to try to work around my problem.

create materialized view epd_comp_mgr_search_ia_mvw refresh complete
start with sysdate
next sysdate + .5
with rowid
as

The materialized views return exactly the same results as the old views. Same number of records, same data, etc. And in much less time.  The problem comes into play when I use these views in a very complex query. I merely substituted the old view name with the new one and then ran it. I get vastly different results from this query than I do using the old views. Specifically, a query that should return 35 records now returns 3.

I'm at a loss to explain it considering the identical data being returned. Do materialized views behave differently when joined to other tables? Can anyone offer a suggestion?

Thanks in advance! Received on Mon Jul 24 2006 - 16:31:14 CDT