Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints)

Put on the trail by Tom Kyte – and after many useful discussions with my colleague Andre, we will take a look in this article at implementing complex business rules declaratively using Materialized Views. A previous post – Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines) – describes how to implement the mandatory master-detail relation (from the Master’s point of view) using Materialized Views.

In this post we will look at several other types of rules:

• Master must have a restricted number of details (no fewer than X and not more than Y) – for example: A Department must have between 2 and 6 employees (or Clerks)
• The Sum of a certain Detail Column is restricted within a master – for example: the Salary Sum per Department may not exceed 20000
• The start-date and end-date of detail-records must be between the Master’s Startdate and Enddate – for example: Project Assignments must not begin before the Project’s startdate or end after the Project’s enddate.

You will see below how each of these rule categories can be implemented declaratively.

Master must have a restricted number of details

We will use EMP and DEPT as our example system. We want to enforce the rule that a Department must have between 2 and 6 employees. Note: we cannot enforce in this way that the department must have at least one employee; for that Mandatory Master-Detail rule, you should look at the earlier post:Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines).

We must make sure that we have the proper constraints in order to create the proper Materialized View logs that will allow Fast Refresh (on commit):

alter table emp add constraint emp_pk primary key(empno);
alter table dept add constraint dept_pk primary key(deptno);
create materialized view log on emp with rowid(deptno) including  new values;
create materialized view log on dept with rowid including new values;

Now that these preprarations are done, we can create the Materialized View itself:

create materialized view emp_mv
refresh fast on commit
as
select deptno, count(*) cnt
from emp
group by deptno;

Let us test this Materialized View – that is refreshed on commit:

select * from emp_mv
/
    DEPTNO        CNT
---------- ----------
        10          3
        20          5
        30          6

Whenever we perform data manipulation on EMP, this Materialized View will be refreshed (or synchronized) with the changes as soon as we commit.

Now we add a Check Constraint on the Materialized View, restricting the value of the CNT column:

alter table emp_mv
add constraint check_cnt
check ( cnt between 2 and 6 )
deferrable;

This specifies that CNT must be between 2 and 6 and that in turn restricts DML on EMP: no insert/update/delete on EMP may lead to a record in EMP_MV where CNT is not between 2 and 6. We can not simply remove Employees from a certain Department or add Employees to a Department:

insert into emp (empno, sal, deptno) values (1111,90,30)
/
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.CHECK_CNT) violated

The Sum of a certain Detail Column is restricted within a master

We will use EMP and DEPT as our example system. We want to enforce the rule that the sum of salaries in a Department must be smaller than 20000.

create materialized view log on emp WITH SEQUENCE , PRIMARY KEY, ROWID (deptno,job,sal) including new values;
create materialized view emp_deptsalsum_mv
refresh fast on commit
as
select deptno
,      sum(sal) sumsal
,      count(*)
,      count(sal) head_count
from   emp
group
by     deptno
/
select * from emp_deptsalsum_mv
/
    DEPTNO     SUMSAL   COUNT(*) HEAD_COUNT
---------- ---------- ---------- ----------
        10    13126.5          3          3
        20      11101          5          5
        30      10451          6          6

Now we add the constraint that will put a cap on the sum of the salaries per department:

alter table emp_deptsalsum_mv
add constraint check_dept_salsum
check ( sumsal < 20000)
deferrable
/
update emp set sal = sal* 2
/
commit
/
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.CHECK_DEPT_SALSUM) violated

It is clear that in this way we have put our business rule in place: any manipulation of the salaries in EMP is at commit time propagated in aggregated form in the Materialized View where it is subsequently subjugated to the check constraint.

The start-date and end-date of detail-records must be between the Master's Startdate and Enddate

Here we make use of another demonstration application with tables Projects and ProjectAssignments Our rule in this particular case will be: Project Assignments must not begin before the Project's startdate or end after the Project's enddate. The tables we will work with:

create table project
( projno int primary key
, start_date date not null
, end_date date not null
)
/
create table emp_proj_assign
( empno int not null
, projno int not null
, start_date date not null
, end_date date not null
, primary key (empno, start_date)
)
/

We know now that we will need Materialized View Logs in order to achieve fast refresh on commit, so here we go:

create materialized view log on project with
rowid(projno,start_date,end_date) including new values
/
create materialized view log on emp_proj_assign with
rowid(projno,start_date,end_date) including new values/
/

The Materialized View used for this situation selects each record from the ProjectAssignments table, joined to its parent Project record. It looks as follows:

create materialized view emp_proj_mv
refresh fast on commit as
select ep.projno
,      ep.start_date ep_start_date
,      ep.end_date ep_end_date
,      p.start_date p_start_date
,      p.end_date   p_end_date
,      ep.rowid ep_rowid
,      p.rowid p_rowid
from   emp_proj_assign ep, project p
where  ep.projno = p.projno
/

The check we need to enforce that the project assignments fall within the limits set by the project’s start_date and end_date now becomes very simple:

alter table emp_proj_mv
add constraint emp_proj_mv_chk1
check (ep_start_date >= p_start_date and ep_end_date < = p_end_date)
deferrable;

Let's now test our new business rule:

insert into project values (1,'01-jan-2004','31-jan-2004');
insert into emp_proj_assign values (1,1,'01-jan-2004','31-jan-2004');
commit;
select *
from emp_proj_mv
/
    PROJNO EP_START_ EP_END_DA P_START_D P_END_DAT EP_ROWID
---------- --------- --------- --------- --------- ------------------
P_ROWID
------------------
         1 01-JAN-04 31-JAN-04 01-JAN-04 31-JAN-04 AAAUYZAAEAAAHzvAAA
AAAUYXAAEAAAHzfAAA

So far so good. I will now try to circumvent the business rule by inserting an assignment that is way outside the project’s timescope:

insert into emp_proj_assign values (1,1,'02-jan-2004','31-may-2004');
commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.EMP_PROJ_MV_CHK1) violated

It appears we have plugged this hole too.

You might object to the fact that we create redundant datastorage just for implementing a business rule. We are in fact currently duplicating data in the Materialized View for no other reason than enforcing a piece of business logic. Well, we can make it leaner by adding a where-condition to the definition of the Materialized: we only need to have the records in the MV that violate the check constraint; the others are completely superfluous

drop materialized view emp_proj_mv
/
create materialized view emp_proj_mv
refresh fast on commit as
select ep.projno
,      ep.start_date ep_start_date
,      ep.end_date ep_end_date
,      p.start_date p_start_date
,      p.end_date   p_end_date
,      ep.rowid ep_rowid
,      p.rowid p_rowid
from emp_proj_assign ep, project p
where ep.projno = p.projno
and not (ep.start_date <= p.start_date and ep.end_date < = p.end_date)  -- NEW CONDITION
/
alter table emp_proj_mv
add constraint emp_proj_mv_chk1
check (ep_start_date <= p_start_date and ep_end_date < = p_end_date)
deferrable
/

Now we achieve the same check, without ever actually loading any data in the MV.
Having come this far, we can further simplify the Materialized View:

drop materialized view emp_proj_mv
/
create materialized view emp_proj_mv
refresh force on commit as
select 1 dummy
from emp_proj_assign ep, project p
where ep.projno = p.projno
and (ep.start_date < p.start_date or ep.end_date > p.end_date)
/
alter table emp_proj_mv
add constraint emp_proj_mv2_chk
check (1=0)
deferrable
/

Combining Mandatory Master Detail and Restricted Aggregate

I have made an attempt to really implement the rule that the number of Employees in a Department must be between 2 and 6 in a single Materialized View – including the requirement that each Department may not have zero Employees. We have to take into account that a MV on a join with aggregation does not allow Fast Refresh On Commit – just like that. We will use a double layered construction to work around that limitation:

create materialized view log on temp.emp WITH SEQUENCE , PRIMARY KEY, ROWID (deptno,job,sal) including new values
/
create materialized view log on temp.dept with SEQUENCE , rowid (deptno) including new values
/
create materialized view emp_dept_mv
refresh force on commit
as
select nvl(e.rowid, d.rowid) pk_rowid
,      e.rowid emp_rowid
,      d.rowid dept_rowid
,      e.empno, d.deptno
,      e.job
,      e.sal
from   emp e
,      dept d
where  e.deptno(+) = d.deptno
/
alter table emp_dept_mv
add constraint emp_dept_pk primary key(pk_rowid)
/
create materialized view log on emp_dept_mv with rowid (pk_rowid, emp_rowid, dept_rowid, empno, deptno, job, sal) including new values
/
create materialized view emp_deptsalsum_mv
refresh force  on commit
as
select deptno
,      sum(sal) sumsal
,      count(*)
,      count(sal) head_count
from   emp_dept_mv
group
by     deptno
/
alter table emp_deptsalsum_mv
add constraint check_dept_count
check ( head_count between 2 and 7)
deferrable
/
insert into dept
(deptno, dname)
values
(50, 'CATERING')
/
pause
commit
/
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.check_dept_count) violated

To really show off, we can now make the Business Rule dynamic or context sensitive: let’s specify the maximum headcount per department:

alter table dept
add ( max_headcount integer)
/
update dept
set    max_headcount = 8
/
update dept
set    max_headcount = 4
where  loc = 'BOSTON'
/

So now the column max_headcount in the DEPT table will specify for each Department what its maximum headcount it. Of course we want to enforce that business rule.

drop materialized view emp_dept_mv
/
create materialized view emp_dept_mv
refresh force on commit
as
select nvl(e.rowid, d.rowid) pk_rowid
,      e.rowid emp_rowid
,      d.rowid dept_rowid
,      e.empno
,      d.deptno
,      d.max_headcount
,      e.job, e.sal
from   emp e
,      dept d
where  e.deptno(+) = d.deptno
/
alter table emp_dept_mv
add constraint emp_dept_pk primary key(pk_rowid)
/
create materialized view log on emp_dept_mv with rowid (pk_rowid, emp_rowid, dept_rowid, empno, deptno,max_headcount, job, sal) including new values
/
drop materialized view emp_deptsalsum_mv
/
create materialized view emp_deptsalsum_mv
refresh force  on commit
as
select deptno
,      sum(sal) sumsal
,      count(*)
,      count(sal) head_count
,      max_headcount
from   emp_dept_mv
group
by     deptno
,      max_headcount
/
alter table emp_deptsalsum_mv
add constraint check_dept_count
check ( head_count between 1 and max_headcount)
deferrable
/
update emp
set    deptno = 40
where  deptno = 20
and    rownum < 5
/
select d.deptno
,      d.max_headcount
,      count(empno) headcount
from   emp e
,      dept d
where  e.deptno(+) = d.deptno
group
by     d.deptno, max_headcount
/
commit
/

Some notes on Materialized Views

You can define triggers on a Materialized View although strictly speaking it is apparently not supported. Trying to perform DML from a trigger made things quite tricky, as this DML is performed while the session is in the Commit phase of a transaction. When I tried to update a table on which I had defined a deferred check constraint in such a way that this constraint was violated, I ran into an Internal Server Error!

One of my ambitions with implementing business rules is to present the application and eventually the end user a complete list of all violations of business rule instead of just giving up on the first error. The way we have implemented the business rules above is very straighforward: the first violation will return an exception to the user with no indication whatsoever what else may be wrong - or even of which record violated the rule.

By using a Before Row trigger on the Materialized View, I can at least identify the record violating the Business Rule:

create or replace trigger pat_br
before delete or insert or update on emp_proj_mv
for each row
begin
  register_br_violation( br=> 'emp_proj_mv2_chk'  , assignment=> :new.ep_rowid);
end;
/
create or replace trigger pat_as
after delete or insert or update on emp_proj_mv
begin
  if any_violations
  then
    insert into raise_exception values (1);
  end if;
end;
/

Now of course we have to remove the check constraint since otherwise we would never get past the first record -although at least this time we would know which record violates the business rule. I have set up a table raise_exception:

create table raise_exception
( a int primary key
)
/
create sequence re_seq
/
create or replace trigger re_br
before insert on raise_exception
for each row
begin
  select re_seq.nextval
  into   :new.a
  from   dual
  ;
end;
/
alter table raise_exception
add constraint re_general_exception check( a is null)
deferrable initially deferred
/

I hoped that because of the deferred check constraint on raise_exception, I can have multiple Materialized Views record their exceptions in this table where the deferred check constraint would only kick in at the very end of the transaction after all Materialized Views have been refreshed and therefore all potential violations of business rules were spotted. Unfortunately, this does not happen. In fact, the deferred check constraint on raise_exception causes severe problems.

commit;
ERROR:
ORA-03114: not connected to ORACLE
commit
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

So far for trying to be very clever.

Another approach with the table raise_exception did not work either – the Materialized View created below did not refresh on commit:

create materialized view log on raise_exception with primary key, rowid including new values
/
drop materialized view re_mv
/
create materialized view re_mv
refresh fast on commit
as
select count(*) c
from raise_exception
/
alter table re_mvadd constraint re_general_exception check( c = 0)
deferrable initially deferred
/

Resources

Data Warehousing: Room with a Better View, Oracle Magazine, March/April 2005 by Arup Nanda – an interesting introduction to Materialized Views in Oracle 10g – basic intro on Query Rewrite, the dbms_mview package and the related Hint – /*+ REWRITE_OR_ERROR */.

AskTom: Declarative IntegrityAs an advocate of enforcing as much integrity as possible declaratively, I am disappointed that little has changed in that arena in 10G. CHECK constraints are still limited to simple single-table, single-row checks, and triggers have to be used to enforce any more complex rules. Do you think Oracle will ever add multi-table check constraints or ASSERTIONS as defined by ANSI SQL? Or has declarative integrity gone about as far as it is going to in Oracle?

AskTom: Create Materialized View Log Semantics

Oracle 9iR2 Data Warehousing Guide – Types of Materialized Views – on which types of MVs (# Materialized Views with Aggregates, Materialized Views Containing Only Joins, Nested Materialized Views) we can discern and what requirements for each of these are. For example:

The SELECT clause in the materialized view creation statement defines the data that the materialized view is to contain. Only a few restrictions limit what can be specified. Any number of tables can be joined together. However, they cannot be remote tables if you wish to take advantage of query rewrite. Besides tables, other elements such as views, inline views (subqueries in the FROM clause of a SELECT statement), subqueries, and materialized views can all be joined or referenced in the SELECT clause. For fast refresh to be possible, the SELECT list must contain all of the GROUP BY columns (if present), and there must be a COUNT(*) and a COUNT(column) on any aggregated columns. Also, materialized view logs must be present on all tables referenced in the query that defines the materialized view. The valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX, and the expression to be aggregated can be any SQL value expression.

On Join-views:

Oracle does not allow self-joins in materialized join views. If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are:

* A materialized view log must be present for each detail table.
* The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.
* If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause. However, if there are outer joins, the WHERE clause cannot have any selections. Further, if there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
* If there are outer joins, unique constraints must exist on the join columns of the inner table. For example, if you are joining the fact table and a dimension table and the join is an outer join with the fact table being the outer table, there must exist unique constraints on the join columns of the dimension table.

If some of these restrictions are not met, you can create the materialized view as REFRESH FORCE to take advantage of fast refresh when it is possible. If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be fast refreshable with respect to the other tables for which all the criteria are met.

A materialized view log should contain the rowid of the master table. It is not necessary to add other columns.

To speed up refresh, you should create indexes on the materialized view’s columns that store the rowids of the fact table.