Environment:
Oracle 12c (12.1.0.2) EE on Exadata Linux
I have an MV that is based on a single table but when the source table is updated and the MV is refreshed I still see a status of 'Needs Compile'.
I recompile the MV and now the status is 'Compile Error'.
I do a refresh on the MV and the status is still 'Compile Error'.
The only way I can get the MV back to a 'VALID' status is to drop and recreate it.
The underlying table object has a 'VALID' status but the MV object does not.
I have run several tests on another MV that I created using just a simple 'select * from <source_table>' from the same source table as above.
The refresh after the source change works nicely and the more simple MV is back in a 'VALID' state after just the refresh operation as I would expect and is seen in other examples.
The big difference between the two MVs is the logic for the first one is more complex and uses many CASE statements and REGEXP statements to create the data that is needed.
Both MVs use all the rows from the source table and have the same number of rows in the MVs as the source table, as expected, planned and hoped for. :-)
Without getting into the weeds of the exact logic of the MVs, my question is more general in that does the more complex logic of the first MV, with the CASE and REGEXP statements cause or force the MV to not get to a 'VALID' state for some reason.
The MV does NOT use any aggregating functions like GROUP BY, etc.
I have read numerous web articles and duplicated several test cases from those articles where everything worked fine.
The data in the MV is actually refreshed correctly but the status is the mystery I'm trying to solve at the moment.
Thanks in advance for any help and ideas!!
-gary
The