Check constraints and AND

Remember the opening statement of the MV error series? It was "Never put more than one error condition into one error message". Two months later I realized I didn't practice what I preach myself.

I'm currently busy developing a Tour de France application at home. The application has to store the standings. After each stage, a daily and overall standing becomes available for each of the following standing types: Individual, point, mountain and team. I'm storing standing lines and I chose a supertype implementation: all in one table. Which means the table looks something like this:

rwijk@ORA11GR1> create table standing_lines
  2  ( stage_id           number(6)   not null
  3  , standing_type      varchar2(2) not null
  4  , overall_or_daily   varchar2(1) not null
  5  , position           number(3)   not null
  6  , rider_id           number(6)
  7  , team_id            number(6)
  8  , duration           interval day(2) to second(0)
  9  , number_of_points   number(4)
 10  , constraint pk primary key (stage_id,standing_type,overall_or_daily,position)
 11  , constraint ck1 check (standing_type in ('IN','PO','MO','TE'))
 12  , constraint ck2 check (overall_or_daily in ('D','O'))
 13  )
 14  /

Table created.

Note that I deliberately left out some unique, foreign key and other check constraints. For each standing type, different columns should be mandatory and other columns should stay null. For example: an individual standing line should contain a rider_id and a duration, and no team_id and no number_of_points. So it seems logical to code some additional check constraints like this:

rwijk@ORA11GR1> alter table standing_lines add constraint ck3 check
  2    (  standing_type != 'IN'
  3    or (   rider_id is not null
  4       and team_id is null
  5       and duration is not null
  6       and number_of_points is null
  7       )
  8    )
  9  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck4 check
  2    (  standing_type != 'PO'
  3    or (   rider_id is not null
  4       and team_id is null
  5       and duration is null
  6       and number_of_points is not null
  7       )
  8    )
  9  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck5 check
  2    (  standing_type != 'MO'
  3    or (   rider_id is not null
  4       and team_id is null
  5       and duration is null
  6       and number_of_points is not null
  7       )
  8    )
  9  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck6 check
  2    (  standing_type != 'TE'
  3    or (   rider_id is null
  4       and team_id is not null
  5       and duration is not null
  6       and number_of_points is null
  7       )
  8    )
  9  /

Table altered.

But when I started coding routines to read some clob content pasted from the official Tour de France site, parsing all standing lines, and inserting the result into the standing_lines table, the inevitable occurred: it didn't work the first time. I received an error message like this:

ORA-02290: check constraint (RWIJK.CK6) violated

So I looked up the definition of this check constraint and all I could say was that I was trying to insert a team standing line and I gave rider_id a value, or I left team_id null, or I left duration null, or I gave number_of_points a value. That's four options. And it's certainly not one error condition per error message. To resolve this error, I first redefined my check constraints and split them into four each.

The first rewrite is by applying the distributivity rule: A or (B and C) == (A or B) and (A or C). In Oracle check constraint logic, it looks like this:

rwijk@ORA11GR1> alter table standing_lines drop constraint ck3
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck4
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck5
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck6
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck3 check
  2    (   ( standing_type != 'IN' or rider_id is not null )
  3    and ( standing_type != 'IN' or team_id is null )
  4    and ( standing_type != 'IN' or duration is not null )
  5    and ( standing_type != 'IN' or number_of_points is null )
  6    )
  7  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck4 check
  2    (   ( standing_type != 'PO' or rider_id is not null )
  3    and ( standing_type != 'PO' or team_id is null )
  4    and ( standing_type != 'PO' or duration is null )
  5    and ( standing_type != 'PO' or number_of_points is not null )
  6    )
  7  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck5 check
  2    (   ( standing_type != 'MO' or rider_id is not null )
  3    and ( standing_type != 'MO' or team_id is null )
  4    and ( standing_type != 'MO' or duration is null )
  5    and ( standing_type != 'MO' or number_of_points is not null )
  6    )
  7  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck6 check
  2    (   ( standing_type != 'TE' or rider_id is null )
  3    and ( standing_type != 'TE' or team_id is not null )
  4    and ( standing_type != 'TE' or duration is not null )
  5    and ( standing_type != 'TE' or number_of_points is null )
  6    )
  7  /

Table altered.

And now it becomes clear we can split them into four parts like this:

rwijk@ORA11GR1> alter table standing_lines drop constraint ck3
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck4
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck5
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck6
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck3 check
  2    ( standing_type != 'IN' or rider_id is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck4 check
  2    ( standing_type != 'IN' or team_id is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck5 check
  2    ( standing_type != 'IN' or duration is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck6 check
  2    ( standing_type != 'IN' or number_of_points is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck7 check
  2    ( standing_type != 'PO' or rider_id is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck8 check
  2    ( standing_type != 'PO' or team_id is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck9 check
  2    ( standing_type != 'PO' or duration is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck10 check
  2    ( standing_type != 'PO' or number_of_points is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck11 check
  2    ( standing_type != 'MO' or rider_id is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck12 check
  2    ( standing_type != 'MO' or team_id is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck13 check
  2    ( standing_type != 'MO' or duration is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck14 check
  2    ( standing_type != 'MO' or number_of_points is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck15 check
  2    ( standing_type != 'TE' or rider_id is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck16 check
  2    ( standing_type != 'TE' or team_id is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck17 check
  2    ( standing_type != 'TE' or duration is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck18 check
  2    ( standing_type != 'TE' or number_of_points is null )
  3  /

Table altered.

I reran my code and the error message became:

ORA-02290: check constraint (RWIJK.CK17) violated

Ah! I tried to insert a team standing line, but failed to give duration a value. Now that's more like it.

This is a very specific example, but you can say in general:

Whenever you have a check constraint and a top-level AND operator, you should split the constraint.

To know whether your expressions contains a top-level AND operator, it's essential you can calculate with boolean expressions. I mentioned the distributivity rule already above, and in that link you find some more useful rules. And I find I apply De Morgan's laws very often as well. These allow you to rewrite your expressions containing less mandatory brackets, so the AND and OR operators will go up in the hierarchy.

Whether you want to apply this rule for a BETWEEN operator (which also contains an AND :-) ) is subjective.

All About Grouping at OOW and UKOUG

My plan for 2009 was to try to do a presentation about grouping in SQL at both Oracle OpenWorld and at UKOUG. I submitted abstracts for both conferences. Unfortunately, the session was not selected for Oracle OpenWorld. Not very surprising really, since it is about functionality that was introduced in versions 8 and 9, so I can imagine other subjects are more important for Oracle. A bit later I read this post by Jonathan Lewis about the UKOUG conference, where Jonathan writes about more than 200 abstracts for 55 slots and a very high standard this year. So I started assuming that I would not be giving my presentation this year at all.

I did receive some good news though: CIBER management selected two people to visit Oracle OpenWorld, and I was one of them. Thanks Erik and Roel! A week later, I was totally surprised but delighted to receive an email telling that my session was selected for UKOUG. The agenda has been put online a couple of days ago, so you can see the proof here. And I was able to reserve a spot for Oracle OpenWorld's Unconference. So now I will be giving the presentation two times.

Details:
- Thursday 15th October 10 AM, at Oracle OpenWorld, Unconference, Moscone West Overlook I
- Wednesday 2nd December 13:50 - 14:50, at UKOUG Conference Series Technology & E-Business Suite, Track 3, Server Technology stream

You can read the UKOUG abstract here. If you are visiting one of those conferences, I hope to see you there.

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 ...

Fast refreshable materialized view errors, part six: MV_CAPABILITIES_TABLE

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_name
  2       , possible
  3       , msgtxt
  4    from mv_capabilities_table
  5   where capability_name like 'REFRESH_FAST%'
  6   order by seq
  7  /

no rows selected

This 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.



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