Previous to Oracle 11g Rel 2, two very common and useful features previously worked well together, they being fast refreshable materialized views and the introduction of virtual columns due to the creation of function-based indexes.

Notice how the materialized view does indeed displayed the correct updated information via the query rewrite operation . So the materialized view behaved and worked as expected even though the underlining master table has a virtual column due to the creation of the function-based index (note that QUERY_REWRITE_INTEGRITY is set to STALE_TOLERATED)

Unfortunately, things go off the rails somewhat since Oracle 11g Rel 2 when a virtual column is introduced due to one of the 11g new features. For example, I now collect some Extended Statistics on this table:

It fails, complaining that the materialized view log is somehow missing a filter column (which it isn’t). We get exactly the same set of issues if we add a visible virtual column via this new 11g capability:

Extended statistics and visible virtual columns are both potentially extremely useful new features introduced in 11g but unfortunately both can not be implemented on any table that needs to be fast refreshable within a complex materialized view.

I raised this issue with Oracle Support who have raised bug 10281402 as a result as it occurs in both 11.2.0.1 and 11.2.0.2 on various platforms I’ve tested.

Share this:

Like this:

Related

Hi Richard,
I’m running into the same problem here.
Did support give you a fix for this issue? I find it very annoying, especially because in many cases the optimizer cannot estimate correctly the cardinality without extended statistics (eg. corraleted columns). However, MVs introduce a great performance benefit in many scenarios and I cannot drop them. MVs + extended statistics would be the best of both worlds.
Thanks,
Gianluca

Hi Richard
I would like to add another thing that is not working with the combination of ‘virtual columns’ and materialized views:
Query Rewrite!
We have function-based indexes in our application (UPPER()). In 10g the query rewrites properly without specifying explicitly the materialized view. In 11g, whenever the optimizer uses the function-based index the query will not be rewritten.
Oracle support says: “Works as designed”. I have opened an enhancement request:
Bug 12345941: ABILITY TO REWRITE QUERY WITH FUNCTION-BASED INDEX
br
Reiner