After you've been confronted with an error message during the creation of your fast refreshable materialized view, you either know exactly what to do next to resolve the error, or not. If not, the MV_CAPABILITIES_TABLE might help. You can read about it in the documentation here. I remember trying to use it in my project half a year ago and I didn't find it useful. After reading this post, you'll probably know what error message I was trying to resolve back then...
The MV_CAPABILITIES_TABLE is much like the PLAN_TABLE. The latter stores the execution plan of a SQL query and its related statistics after issuing an explain plan statement. The MV_CAPABILITIES_TABLE stores the capabilities, among which the capability to fast refresh, after issuing an dbms_mview.explain_mview statement. You can install the MV_CAPABILITIES_TABLE by running the utlxmv.sql script.
Here is how it works when the fast refreshable materialized view doesn't experience any problems. It is the same setup as in part one:
rwijk@ORA11GR1> create table myemp as select * from emp
2 /
Table created.
rwijk@ORA11GR1> alter table myemp add primary key (empno)
2 /
Table altered.
rwijk@ORA11GR1> create materialized view log on myemp
2 with primary key, rowid
3 /
Materialized view log created.
rwijk@ORA11GR1> truncate table mv_capabilities_table
2 /
Table truncated.
Now wrap the entire "create materialized view" statement as an argument to the explain_mview procedure:
rwijk@ORA11GR1> begin
2 dbms_mview.explain_mview
3 ( 'create materialized view emp_mv
4 refresh fast on commit
5 as
6 select empno
7 , ename
8 , job
9 , sal
10 , deptno
11 from myemp
12 where deptno = 10'
13 );
14 end;
15 /
PL/SQL procedure successfully completed.
rwijk@ORA11GR1> select *
2 from mv_capabilities_table
3 order by seq
4 /
STATEMENT_ID MVOWNER
------------------------------ ------------------------------
MVNAME CAPABILITY_NAME P
------------------------------ ------------------------------ -
RELATED_TEXT
--------------------------------------------------------------------------------
RELATED_NUM MSGNO
----------- ----------
MSGTXT
--------------------------------------------------------------------------------
SEQ
----------
RWIJK
EMP_MV PCT N
1
RWIJK
EMP_MV REFRESH_COMPLETE Y
1002
RWIJK
EMP_MV REFRESH_FAST Y
2003
RWIJK
EMP_MV REWRITE N
3004
RWIJK
EMP_MV PCT_TABLE N
MYEMP
155 2068
relation is not a partitioned table
4005
RWIJK
EMP_MV REFRESH_FAST_AFTER_INSERT Y
5006
RWIJK
EMP_MV REFRESH_FAST_AFTER_ONETAB_DML Y
6007
RWIJK
EMP_MV REFRESH_FAST_AFTER_ANY_DML Y
7008
RWIJK
EMP_MV REFRESH_FAST_PCT N
2157
PCT is not possible on any of the detail tables in the materialized view
8009
RWIJK
EMP_MV REWRITE_FULL_TEXT_MATCH N
2159
query rewrite is disabled on the materialized view
9010
RWIJK
EMP_MV REWRITE_PARTIAL_TEXT_MATCH N
2159
query rewrite is disabled on the materialized view
10011
RWIJK
EMP_MV REWRITE_GENERAL N
2159
query rewrite is disabled on the materialized view
11012
RWIJK
EMP_MV REWRITE_PCT N
2158
general rewrite is not possible or PCT is not possible on any of the detail tabl
es
12013
RWIJK
EMP_MV PCT_TABLE_REWRITE N
MYEMP
155 2068
relation is not a partitioned table
13014
14 rows selected.
This materialized view is fast refreshable. You can tell because the column "Possible" contains a 'Y' value for the REFRESH_FAST% capabilities.
The MV capabilities are roughly about three features: fast refresh, query rewrite and partition change tracking (wonderful feature, by the way!). This series is about fast refresh, so from now on I'll use the predicate "capability_name like 'REFRESH_FAST%'".
I will now repeat all situations of the previous five posts, only skipping the ones that didn't result in an error. You can imagine it can become extremely lengthy, so I'll only write the restriction, the error message and the result of doing this sequence:
truncate table mv_capabilities_table
/
begin
dbms_mview.explain_mview
( 'create materialized view ...'
);
end;
/
select capability_name
, possible
, msgtxt
from mv_capabilities_table
where capability_name like 'REFRESH_FAST%'
order by seq
/
At the end of this post, you'll find a link to the script used to receive this output, so you can replay it on your own database, and see for yourself.
Restriction 1 for basic MV fast refresh: The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: the dbms_mview.explain_mview errors out with an ORA-32342:
rwijk@ORA11GR1> begin
2 dbms_mview.explain_mview
3 ( 'create materialized view emp_mv
4 refresh fast on commit
5 as
6 select empno
7 , ename
8 , job
9 , sal
10 , deptno
11 , sysdate now
12 from myemp
13 where deptno = 10'
14 );
15 end;
16 /
begin
*
ERROR at line 1:
ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view
statement
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
ORA-06512: at "SYS.DBMS_XRWMV", line 22
ORA-06512: at "SYS.DBMS_XRWMV", line 42
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3299
ORA-06512: at line 2
And the MV_CAPABILITIES TABLE remains empty:
rwijk@ORA11GR1> select capability_nameThis situation will be encountered much more often, so this will be shortened to "ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement" from now on.
2 , possible
3 , msgtxt
4 from mv_capabilities_table
5 where capability_name like 'REFRESH_FAST%'
6 order by seq
7 /
no rows selected
Restriction 3 for basis MV fast refresh: It cannot contain a SELECT list subquery.
Error message: ORA-22818: subquery expressions not allowed here
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Restriction 4 for basic MV fast refresh: It cannot contain an analytic function.
Error message: ORA-12052: cannot fast refresh materialized view RWIJK.EMP_MV
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
window function in mv
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
The "ORA-12052: cannot fast refresh materialized view X" does not tell you why it cannot refresh, but the MV capabilities table tells you "window function in MV" as the reason. So it's worth using in this case.
Restriction 5 for basic MV fast refresh: It cannot contain a MODEL clause.
Error message: ORA-12052: cannot fast refresh materialized view RWIJK.EMP_MV
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
a SPREADSHEET clause is present
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
"a SPREADSHEET clause is present". Nice name for the model clause, but again you are getting more information from the MV_CAPABILITIES_TABLE.
Restriction 7 for basic MV fast refresh: It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement.
Restriction 8 for basic MV fast refresh: It cannot contain a [START WITH à] CONNECT BY clause.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view.
Restriction 10 for basis MV fast refresh: ON COMMIT materialized views cannot have remote detail tables.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
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.
Error message: ORA-12052: cannot fast refresh materialized view RWIJK.EMPDEPT_MV
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
the SELECT list does not have the rowids of all the detail tables
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
Again, the ORA-12052 error message leads to a much clearer message in the capabilities table.
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.
Error message: ORA-23413: table "RWIJK"."MYDEPT" does not have a materialized view log
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
The error message was already very clear, and the contents of the capabilities table is so as well.
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.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Restriction 6 for join MV fast refresh: ANSI joins are not possible
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Restriction 2 for aggregate MV fast refresh:All tables in the materialized view must have materialized view logs
Error message: ORA-23413: table "RWIJK"."MYEMP" does not have a materialized view log
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
Restriction 3 for aggregate MV fast refresh:The materialized view logs must contain all columns from the table referenced in the materialized view
Error message: ORA-12033: cannot use filter columns from materialized view log on
"RWIJK"."MYEMP"
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
mv log does not have all necessary columns
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
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.
Error message: ORA-32412: encrypted column "DEPTNO" not allowed in the materialized view log
MV capabilities: because the MV log could not even be created, it's not possible to collect a message from the MV_CAPABILITIES_TABLE.
Restriction 5 for aggregate MV fast refresh:All tables in the materialized view must have materialized view logs specified with ROWID
Error message: ORA-12032: cannot use rowid column from materialized view log on
"RWIJK"."MYEMP"
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
mv log must have ROWID
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
Restriction 6 for aggregate MV fast refresh:All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES.
Error message: ORA-32401: materialized view log on "RWIJK"."MYEMP" does not have new values
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
mv log must have new values
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
Restriction 8 for aggregate MV fast refresh:Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
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.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
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.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Restriction 13 for aggregate MV fast refresh:The SELECT list must contain all GROUP BY columns.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
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.
Error message: none; MV gets created but just doesn't fast refresh
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML N
SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N
mv log does not have sequence #
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
This one is interesting and explains why the MV gets created, but doesn't (always) fast refresh: REFRESH_FAST and REFRESH_FAST_AFTER_INSERT are possible, but REFRESH_FAST_AFTER_ONETAB_DML is not possible. According to the documentation, the latter means:
If this capability is possible, fast refresh from a materialized view log is possible regardless of the type of update operation, provided all update operations are performed on a single table. If this capability is not possible, fast refresh from a materialized view log may not be possible when the update operations are performed on multiple tables.If you look closely at my test of restriction 15 in the third post of this series, then you see I only perform one update on one table, so I am at a loss here as to why that single update doesn't cause the MV to get refreshed after commit. But at least the MV_CAPABILITIES_TABLE warns you by saying "SUM(expr) without COUNT(expr)".
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.
Error message: none; MV gets created but just doesn't fast refresh
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML N
COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ANY_DML N
mv log does not have sequence #
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
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.
Error message: none; MV gets created but just doesn't fast refresh
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML N
mv uses the MIN or MAX aggregate functions
REFRESH_FAST_AFTER_ANY_DML N
mv log does not have sequence #
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
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.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
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)".
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
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.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
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.
Error message: none; MV gets created, but just doesn't fast refresh after modification on the inner table
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML N
mv log does not have sequence #
REFRESH_FAST_AFTER_ANY_DML N
mv log does not have sequence #
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
And after adding "sequence" to the materialized view log, it says:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
So no additional clue as to why the refresh doesn't happen: the MV_CAPABILITIES_TABLE indicates all is well.
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.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Note that in part three, the MV was created first, then the primary key was dropped. This lead to an MV that didn't refresh. Here I changed the order by first dropping the primary key and then trying to create the MV. This time it led to the above mentioned ORA-12054.
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.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
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.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Restriction 26 for aggregate MV fast refresh:It cannot contain a HAVING clause with a subquery.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Restriction 1 for union all MV fast refresh:The defining query must have the UNION ALL operator at the top level.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
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.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Restriction 5 for union all MV fast refresh:Insert-only aggregate materialized view queries are not supported for materialized views with UNION ALL.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Restriction 6 for union all MV fast refresh:Remote tables are not supported for materialized views with UNION ALL.
Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
Restriction 1 for nested MV fast refresh:All parent and base materialized views must contain joins or aggregates.
Error message: ORA-12053: this is not a valid nested materialized view
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
requirements not satisfied for fast refresh of nested mv
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
"Requirements not satisfied for fast refresh of nested MV". Now that's not very helpful, is it? Exactly the same is said by the 'N' in the Possible column.
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.
Error message: ORA-23413: table "RWIJK"."PARENT1_MV" does not have a materialized view log
MV capabilities:
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
Restriction 3 for nested MV fast refresh:You cannot create both a materialized view and a prebuilt materialized view on the same table.
Error message: ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV
MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement
The above shows that situations exist in which the MV_CAPABILITIES_TABLE gives more precise information about what restriction you are hitting, and it shows much more information as well.
The next part will summarize all findings by starting with the error message and collecting all encountered error conditions. Hopefully that will be a very useful post for people developing fast refreshable views.
The script
