RE: Problems w/ materialized view.....

There appear to be about 7 bugs logged since August of this year that relate to views returning wrong results. The ones related to outer joins, function based indexes, and to case statements (if the underlying problem could also apply to a decode) seem most likely:

1. WRONG RESULTS FROM QUERY JOINING ON A VIEW WITH CONTAINS CLAUSE AND NULL JOIN 3569917 30-SEP-2004 Oracle Server - Enterprise Edition Solaris Operating System (SPARC 64-bit) BUG
2. DETERMINISTIC HINT IS IGNORED 3841748 19-AUG-2004 Oracle Server - Enterprise Edition Solaris Operating System (SPARC 64-bit) BUG
3. NEED EVENT TO REVERT PREC/SCALE CHANGE IN 9.2.0.5 FOR OCIDESCRIBE() 3807954 05-AUG-2004 Oracle Server - Enterprise Edition Microsoft Windows 2000 BUG
4. WRONG RESULTS WITH CASE AND LIKE 'STRING%' 3858269 13-SEP-2004 Oracle Server - Enterprise Edition Linux x86 BUG
5. WRONG RESULTS WITH VIEW NOT MERGED AND FUNCTION BASED INDEX 3847585 11-OCT-2004 Oracle Server - Enterprise Edition AIX5L Based Systems
   (64-bit) BUG
   
6. WRONG RESULTS WITH HASH JOIN OUTER 3892220 08-OCT-2004 Oracle Server - Enterprise Edition HP-UX PA-RISC
   (64-bit) BUG
   
7. WRONG RESULTS WITH DISTINCT COUNT AND UNNESTED EXISTS SUBQUERY 3637624 17-AUG-2004 Oracle Server - Enterprise Edition Microsoft Windows 2000 BUG
   • Mark D Powell --

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark Sent: Tuesday, October 12, 2004 10:51 AM To: Parker, Matthew; Oracle-L Mailing List (E-mail 2) Subject: RE: Problems w/ materialized view.....

Matthew,

I understand the implications of the different points in time, and that = this could happen if DML is occurring on any of the underlying tables.

That's not the case. I can repeatedly select from the view and get the = exact same number of rows and I can repeatedly drop and re-create the = m-view
and select exactly the same number of rows from it every time.

This is weird.

I'm now thinking it's not actually an m-view problem, but a view = problem. I can also demonstrate the problem this way:

<snip>

--
http://www.freelists.org/webpage/oracle-l