Posted by One of the toughest problems in database applications is validation of data that extends “across” rows. We have declarative constraints for many of the validations we need to do for a single row, eg
- “I must be unique”
- “I must have a non-null value in this column”
- “I must be a valid member of the foreign key related set”
and so forth. But the moment that validation rule extends across rows, such as
- “There can be at most 10 employees per department”
- “My salary cannot be higher that any manager in my department”
the database leaves us to our own devices, with no declarative facilities available.
Sidenote: If you’d like that to change, make sure you visit the community ideas page on assertions.
One of the most common but incorrect implementations of multi-row or multi-table “constraints” is by using triggers. There is no inherent problem with using triggers to achieve such constraints; the issue is that so many implementations using triggers fail to take into the necessary locking requirements due to Oracle’s read consistency model. Since you can’t see uncommitted changes in another session, then failing to take this into account inevitably leads to a corrupted database.
For some validation requirements, we can achieve the desired result without triggers by taking advantage of a feature of materialized views that lets them be defined as REFRESH ON COMMIT. In this way, as long as the materialized view can be defined in such a way to validate our multi-row data, then the fact that the refresh takes place at the conclusion of a transaction, we can guarantee that the data is valid at commit time.
But you still need to take care when using this approach, because we might silently fail to refresh a materialized view because of shortfalls in its definition. Lets take a look at such an example.
I have an ORDERS table, which takes orders and items within an order. The column of importance in this demo is the PERCENTAGE column. For a given order, the percentage across all items for that order must sum to 100 percent. This means that multiple rows are involved in terms of validating this rule, and thus a standard declarative constraint will not be possible.
SQL> create table orders
2 (
3 order_date date not null
4 ,order_id int not null
5 ,item_id int not null
6 ,percentage int not null
7 ,constraint orders_pk primary key (order_date,order_id,item_id)
8 );
Table created.
SQL>
SQL> insert into orders(order_date,order_id,item_id,percentage) values (date '2021-01-01',1,1,50);
1 row created.
SQL>
SQL> insert into orders(order_date,order_id,item_id,percentage) values (date '2021-01-01',1,2,50);
1 row created.
The 2 rows at 50 percent each are fine, but that is by good fortune rather than any database validation. I will now build a fast refresh materialized view which aggregates the percentage based on each order ID. This sum should always be 100, and since this will now be a single row on the materialized view, I can use a check constraint to ensure that this is the case.
SQL> create materialized view log on orders
2 with rowid, (percentage) including new values;
Materialized view log created.
SQL>
SQL> create materialized view orders_mv
2 refresh fast
3 on commit
4 as
5 select order_date,order_id, sum(percentage) percentage
6 from orders
7 group by order_date,order_id;
Materialized view created.
SQL> alter materialized view orders_mv
2 add constraint percentage_is_100_x
3 check (percentage=100) deferrable;
Materialized view altered.
So at this point, I should not be able to commit a transaction where the percentages do not total to 100, because when I refresh the materialized view it will violate the constraint. Lets test that:
SQL> update orders set percentage=75 where item_id=1;
1 row updated.
SQL> commit;
Commit complete.
Well…that’s a disaster because (a) the invalid data was allowed, and (b) we were given no indication that anything has gone awry. In fact, we would need to dig into the alert log for the database to discover that something has indeed gone wrong
PDB1(3):On commit
PDB1(3):MV MCDONAC.PCT_MV was not refreshed successfully.
PDB1(3):Number of MV refresh failures: 1.
PDB1(3):Encountered error ORA-12008.
2022-01-31T10:25:58.000437+08:00
PDB1(3):Fast refresh is not possible for on-commit mv MCDONAC.PCT_MV
PDB1(3):On commit
This is where the DBMS_MVIEW.EXPLAIN_MVIEW utility is very useful. You can simply pass in the name of a materialized view, or a query that will ultimately become a materialized view and you get a report on its capabilities. If I run that on the materialized view I defined previously:
SQL> exec dbms_mview.explain_mview('orders_mv');
PL/SQL procedure successfully completed.
SQL> select * from MV_CAPABILITIES_TABLE
2 @pr
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 1
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_COMPLETE
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 1002
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 2003
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 3004
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : PCT_TABLE
POSSIBLE : N
RELATED_TEXT : ORDERS
RELATED_NUM : 62
MSGNO : 2068
MSGTXT : relation is not a partitioned table
SEQ : 4005
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_INSERT
POSSIBLE : N
RELATED_TEXT : MCDONAC.ORDERS
RELATED_NUM :
MSGNO : 2166
MSGTXT : problem with loader log
SEQ : 5006
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_INSERT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2164
MSGTXT : the materialized view is BUILD DEFERRED
SEQ : 5007
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ONETAB_DML
POSSIBLE : N
RELATED_TEXT : PERCENTAGE
RELATED_NUM : 28
MSGNO : 2143
MSGTXT : SUM(expr) without COUNT(expr)
SEQ : 6008
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ONETAB_DML
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2146
MSGTXT : see the reason why REFRESH_FAST_AFTER_INSERT is disabled
SEQ : 6009
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ONETAB_DML
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2142
MSGTXT : COUNT(*) is not present in the select list
SEQ : 6010
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ONETAB_DML
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2143
MSGTXT : SUM(expr) without COUNT(expr)
SEQ : 6011
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ANY_DML
POSSIBLE : N
RELATED_TEXT : MCDONAC.ORDERS
RELATED_NUM :
MSGNO : 2165
MSGTXT : mv log does not have sequence #
SEQ : 7012
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ANY_DML
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2161
MSGTXT : see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
SEQ : 7013
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2157
MSGTXT : PCT is not possible on any of the detail tables in the materialized view
SEQ : 8014
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2164
MSGTXT : the materialized view is BUILD DEFERRED
SEQ : 8015
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_FULL_TEXT_MATCH
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2159
MSGTXT : query rewrite is disabled on the materialized view
SEQ : 9016
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_FULL_TEXT_MATCH
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2164
MSGTXT : the materialized view is BUILD DEFERRED
SEQ : 9017
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_PARTIAL_TEXT_MATCH
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2159
MSGTXT : query rewrite is disabled on the materialized view
SEQ : 10018
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_PARTIAL_TEXT_MATCH
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2164
MSGTXT : the materialized view is BUILD DEFERRED
SEQ : 10019
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_GENERAL
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2159
MSGTXT : query rewrite is disabled on the materialized view
SEQ : 11020
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_GENERAL
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2164
MSGTXT : the materialized view is BUILD DEFERRED
SEQ : 11021
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2158
MSGTXT : general rewrite is not possible or PCT is not possible on any of the detail tables
SEQ : 12022
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : PCT_TABLE_REWRITE
POSSIBLE : N
RELATED_TEXT : ORDERS
RELATED_NUM : 62
MSGNO : 2068
MSGTXT : relation is not a partitioned table
SEQ : 13023
PL/SQL procedure successfully completed.
you can see that most of the “refresh after DML” capabilities are set to “NO”. This is why the refresh did not take place, but notice that we did not stop you from creating the materialized view. This is because we don’t know in advance what your intentions for the object are. Perhaps you’ll never fast refresh it. Perhaps you’ll never need it for query rewrite etc.
But we also get clues as to what fixes are needed to the materialized view.
- SUM(expr) without COUNT(expr)
- mv log does not have sequence #
- COUNT(*) is not present in the select list
So we can take that advice and refactor our materialized view log and our materialized view. First I’ll get our data back to being correct
SQL> update orders set percentage=50 where item_id=1;
1 row updated.
SQL> drop materialized view orders_mv;
Materialized view dropped.
SQL> drop materialized view log on orders;
Materialized view log dropped.
SQL> create materialized view log on orders
2 with rowid, sequence, (percentage) including new values;
Materialized view log created.
SQL> create materialized view orders_mv
2 refresh fast
3 on commit
4 as
5 select order_date,order_id, sum(percentage) percentage, count(percentage) c1, count(*) c2
6 from orders
7 group by order_date,order_id;
Materialized view created.
Now I can re-run DBMS_MVIEW to check on the capabilities of the revised materialized view.
SQL> exec dbms_mview.explain_mview('orders_mv');
PL/SQL procedure successfully completed.
SQL> select * from MV_CAPABILITIES_TABLE
2 @pr
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 1
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_COMPLETE
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 1002
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 2003
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 3004
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : PCT_TABLE
POSSIBLE : N
RELATED_TEXT : ORDERS
RELATED_NUM : 97
MSGNO : 2068
MSGTXT : relation is not a partitioned table
SEQ : 4005
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_INSERT
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 5006
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ONETAB_DML
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 6007
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ANY_DML
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 7008
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REFRESH_FAST_PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2157
MSGTXT : PCT is not possible on any of the detail tables in the materialized view
SEQ : 8009
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_FULL_TEXT_MATCH
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2159
MSGTXT : query rewrite is disabled on the materialized view
SEQ : 9010
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_PARTIAL_TEXT_MATCH
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2159
MSGTXT : query rewrite is disabled on the materialized view
SEQ : 10011
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_GENERAL
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2159
MSGTXT : query rewrite is disabled on the materialized view
SEQ : 11012
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : REWRITE_PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2158
MSGTXT : general rewrite is not possible or PCT is not possible on any of the detail tables
SEQ : 12013
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : ORDERS_MV
CAPABILITY_NAME : PCT_TABLE_REWRITE
POSSIBLE : N
RELATED_TEXT : ORDERS
RELATED_NUM : 97
MSGNO : 2068
MSGTXT : relation is not a partitioned table
SEQ : 13014
PL/SQL procedure successfully completed.
That looks promising, so lets add the check constraint back on, and retry our invalid UPDATE again.
SQL> alter materialized view orders_mv
2 add constraint percentage_is_100_x
3 check (percentage=100) deferrable;
Materialized view altered.
SQL> update orders set percentage=51 where item_id=1;
1 row updated.
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (MCDONAC.PERCENTAGE_IS_100_X) violated
So be careful when using fast refresh materialized views as a means of adding more robustness to your data validation. Errors may be silently missed unless you have the definitions correct for the requirements you need your materialized view to serve.
Learning is not a spectator sport 
Hello Connor!
Thank you for sharing this post, it is very useful!
Also, could you please share “pr” script?
Head over to https://github.com/connormcd/misc-scripts/tree/master/sql
Thank you very much!
Hi Connor,
I think that the mere fact that Oracle allows the mview to be created as REFRESH FAST ON COMMIT
is a bug that should be corrected.
For example, if you try to create the mview without a proper materialized view log, then it fails, as shown below:
create materialized view orders_mv
refresh fast
on commit
as
select order_date,order_id, sum(percentage) percentage
from orders
group by order_date,order_id
/
ORA-23413: table “SQL_CMRAEOZNHKPBXKQPQFPOBACNF”.”ORDERS” does not have a materialized view log
create materialized view log on orders
with rowid, (percentage)
/
Statement processed.
create materialized view orders_mv
refresh fast
on commit
as
select order_date,order_id, sum(percentage) percentage
from orders
group by order_date,order_id
/
ORA-32401: materialized view log on “SQL_CMRAEOZNHKPBXKQPQFPOBACNF”.”ORDERS” does not have new values
So, it is not justified that Oracle does allow the mview to be created without the proper definition.
Second, with the “incomplete” mview in place, attempting an incorrect transaction containing inserts only
does cause the COMMIT to fail, with the expected error:
insert into orders(order_date,order_id,item_id,percentage) values (date ‘2021-01-01’,1,1,50);
insert into orders(order_date,order_id,item_id,percentage) values (date ‘2021-01-01’,1,2,30);
commit;
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at “SYS.DBMS_SQL”, line 1721
So, in my opinion, there is no reason to have the UPDATE behave differently.
I think that this is also an even more severe bug that should be corrected.
Thanks a lot & Best Regards,
Iudith Mentzel
Hi Connor again,
Another remark, in continuation of my previous one:
Regardless of the issue of using the mview for cross-row validation,
if we define the mview with REFRESH FAST only, but without the ON COMMIT,
then the fast refresh is able to detect the incomplete mview definition, BEFORE even reaching the phase of
validating the CHECK constraint:
update orders set percentage=75 where item_id=1;
commit;
1 row(s) updated.
Statement processed.
begin
dbms_mview.refresh(‘orders_mv’);
end;
/
ORA-32314: REFRESH FAST of “SQL_CQBIWZXUOVMRXBGTEPGNPYRAH”.”ORDERS_MV” unsupported after deletes/updates
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3012
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2424
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 88
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 253
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2405
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2968
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3255
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3287
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 16
ORA-06512: at line 2
ORA-06512: at “SYS.DBMS_SQL”, line 1721
I don’t see why the addition of ON COMMIT makes the mview “lose” this ability.
Cheers & Best Regards,
Iudith