Fast refreshable materialized view errors, part seven: a summary

Previous posts:

Fast refreshable materialized view errors, part one (basis MV's)
Fast refreshable materialized view errors, part two: join MV's
Fast refreshable materialized view errors, part three: aggregate MV's
Fast refreshable materialized view errors, part four: union all MV's
Fast refreshable materialized view errors, part five: nested MV's
Fast refreshable materialized view errors, part six: MV_CAPABILITIES_TABLE


This post summarizes the previous posts by reversing the angle. You are developing fast refreshable materialized views and you experience a problem: either an error message appears when creating the MV or the MV gets created but just doesn't fast refresh. What should you do? Normally, the error message should tell you all about it, but as indicated in part one, this isn't always the case with materialized view errors. The list below will hopefully help in resolving your error.

This list may appear pretty complete after you've read all previous entries, but it is certainly not, due to several reasons:
- I could reproduce most error conditions, but not all.
- After having them reproduced, it's not certain the entire restriction was covered.
- There probably are a few undocumented restrictions. I've already encountered a few of them.
- The documentation has shown itself to be outdated and not entirely complete, so you cannot rely on that.
However, I hope to have covered at least the majority of error conditions, so there is a good chance your situation is covered.


ORA-12032: cannot use rowid column from materialized view log on "RWIJK"."MYEMP"

A rather cryptic way to say the rowid column is missing from the materialized view log. I only saw this restriction mentioned for aggregate MV's (restriction 5: All tables in the materialized view must have materialized view logs specified with ROWID), but it should be true as well for union all MV's and join MV's. The error message is quite clear, but if you'd do an explain_mview you'd see the even clear message "mv log must have ROWID" for the
REFRESH_FAST_AFTER_INSERT capability.


ORA-12033: cannot use filter columns from materialized view log on "RWIJK"."MYEMP"

You violated restriction 3 for aggregate MV fast refresh: the materialized view logs must contain all columns from the table referenced in the materialized view. Again I think the error message is worded a little poorly. The REFRESH_FAST_AFTER_INSERT capability says it better: mv log does not have all necessary columns.


ORA-12052: cannot fast refresh materialized view RWIJK.EMP_MV

You want to know why, so this error message is not helpful. You probably violated one of the three restrictions below:

1) Restriction 4 for basic MV fast refresh: It cannot contain an analytic function. The REFRESH_FAST_AFTER_INSERT capability says: window function in mv.

2) Restriction 5 for basic MV fast refresh: It cannot contain a MODEL clause. The REFRESH_FAST_AFTER_INSERT capability says: a SPREADSHEET clause is present.

3) Restriction 2 for join MV fast refresh: Rowids of all the tables in the FROM list must appear in the SELECT list of the query. The REFRESH_FAST_AFTER_INSERT capability says: the SELECT list does not have the rowids of all the detail tables.

So when you encounter an ORA-12052, do an explain_mview to really see why your MV cannot be fast refreshed.


ORA-12053: this is not a valid nested materialized view

And why is this not a valid nested materialized view? You probably violated restriction 1 for nested MV fast refresh:All parent and base materialized views must contain joins or aggregates. An explain_mview is not helpful here, as the REFRESH_FAST_AFTER_INSERT capability says: requirements not satisfied for fast refresh of nested mv. As could be seen in part five, this message means one of your parent MV's is a basic MV.


ORA-22818: subquery expressions not allowed here

You probably violated restriction 3 for basis MV fast refresh: It cannot contain a SELECT list subquery. A clear error message. An explain_mview cannot be done; it gives error message ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement.


ORA-23413: table "RWIJK"."MYDEPT" does not have a materialized view log

This error message cannot be more clear: you need materialized view logs on the base tables/MV's and they are missing. This error message corresponds with:
- Restriction 4 for join MV fast refresh: Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
- Restriction 2 for aggregate MV fast refresh: All tables in the materialized view must have materialized view logs
- Restriction 2 for nested MV fast refresh: All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log.


ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV

You violated restriction 3 for nested MV fast refresh:You cannot create both a materialized view and a prebuilt materialized view on the same table. A clear error message, so it doesn't matter that this one cannot be further explained by explain_mview.


ORA-32401: materialized view log on "RWIJK"."MYEMP" does not have new values

You violated restriction 6 for aggregate MV fast refresh: All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES. If you do an explain_mview, the REFRESH_FAST_AFTER_INSERT capability says: mv log must have new values.


ORA-32412: encrypted column "DEPTNO" not allowed in the materialized view log

You violated restriction 4 for aggregate MV fast refresh: None of the columns in the base table, referred to in the materialized view log, can be encrypted.


The materialized view is created, but just doesn't fast refresh

You probably violated one of the four restrictions below:

1) Restriction 15 for aggregate MV fast refresh: If the materialized view has SUM(expr) but no COUNT(expr), then fast refresh is supported only on conventional DML inserts and direct loads. In this case the REFRESH_FAST_AFTER_ONETAB_DML capability says: SUM(expr) without COUNT(expr).

2) Restriction 16 for aggregate MV fast refresh: If the materialized view has no COUNT(*), then fast refresh is supported only on conventional DML inserts and direct loads. In this case, the REFRESH_FAST_AFTER_ONETAB_DML capability says: COUNT(*) is not present in the select list.

3) Restriction 17 for aggregate MV fast refresh: A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause. In this case the REFRESH_FAST_AFTER_ONETAB_DML capability says: mv uses the MIN or MAX aggregate functions.

4) Restriction 22 for aggregate MV fast refresh: Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. In this case the MV_CAPABILITIES_TABLE says everything is ok.

So by using the MV_CAPABILITIES_TABLE, there is a good chance you may find out more about this situation.


ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

You probably violated one of the restrictions below:

• Restriction 1 for basic MV fast refresh: The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.
• Restriction 7 for basic MV fast refresh: It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.
• Restriction 8 for basic MV fast refresh: It cannot contain a [START WITH ...] CONNECT BY clause.
• Restriction 10 for basis MV fast refresh: ON COMMIT materialized views cannot have remote detail tables.
• Restriction 5 for join MV fast refresh: You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.
• Restriction 6 for join MV fast refresh: ANSI joins are not possible
• Restriction 8 for aggregate MV fast refresh: Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.
• Restriction 10 for aggregate MV fast refresh: Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.
• Restriction 12 for aggregate MV fast refresh: If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified.
• Restriction 13 for aggregate MV fast refresh: The SELECT list must contain all GROUP BY columns.
• Restriction 18 for aggregate MV fast refresh: For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.
• Restriction 19 for aggregate MV fast refresh: For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".
• Restriction 21 for aggregate MV fast refresh: The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.
• Restriction 23 for aggregate MV fast refresh: Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided unique constraints exist on the join columns of the inner join table.
• Restriction 24 for aggregate MV fast refresh: If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
• Restriction 25 for aggregate MV fast refresh: Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged.
• Restriction 26 for aggregate MV fast refresh: It cannot contain a HAVING clause with a subquery.
• Restriction 1 for union all MV fast refresh:The defining query must have the UNION ALL operator at the top level.
• Restriction 3 for union all MV fast refresh:The SELECT list of each query must include a UNION ALL marker, and the UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block.
• Restriction 5 for union all MV fast refresh:Insert-only aggregate materialized view queries are not supported for materialized views with UNION ALL.
• Restriction 6 for union all MV fast refresh:Remote tables are not supported for materialized views with UNION ALL.

Good luck in determining which one applies to your situation :-)

This is horrible of course. The ORA-12054 basically means: sorry, you are out of luck. And don't bother trying to explain the materialized view, because that just leads to an "ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement" message. Of course it would be nice if Oracle provided one error message per violated restriction. We can but hope ...