First of all, I can't seem to create a materialized view containing self-joins AND union all. Is it possible?
I checked Oracle 9i (my version: PL/SQL Release 220.127.116.11.0 - Production) documentation and I get different answers (or so it seems to me).
First I saw this: "The COMPATIBILITY parameter must be set to 9.0 if the materialized aggregate view has inline views, outer joins, self joins or grouping sets and FAST REFRESH is specified during creation..."
Did you see the part about 'self joins' in there? I did and I was pumped because that seems to say that you CAN have 'self joins' (and my compatibility is 9.2...)
In the very same document I also found "Oracle does not allow self-joins in materialized join views." (rage)
You can see the document I am speaking of here: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#574889
Whenever I try to create the mview I get the following error: ORA-01446 cannot select ROWID from view with DISTINCT, GROUP BY, etc.
That's because the oracle documentation says I need to use rowid in my mview if I am using 'union all'