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

7

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 &lt;= 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 &lt;= 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 &lt; 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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

7 Comments

  1. Pingback: AMIS Technology blog » Blog Archive » On the false sense of security with PL/SQL based implementation of business rules - and what to do about it

  2. Pingback: AMIS Technology blog » Blog Archive » The Hunt for the Oracle Database On Commit trigger

  3. Pingback: AMIS Technology blog » Blog Archive » Oracle 7Up to 10g - How time flies… Training for seasoned Oracle SQL and PL/SQL Developers

  4. Alex Nuijten on

    Hi Shafi,
    Do you have SUM and COUNT columns in your TEMPTRAN table? Or is this a typo and did you want something like:

    CREATE MATERIALIZED VIEW MV_TEMPTRAN
    REFRESH FAST ON COMMIT
    AS
    SELECT LEDGERCD, SUBLEDCD, BRCD, VALUE_DATE, VCHCODE,
    SUM (BALANCE), COUNT (CNT), --< ------ Note the parenthesis
    COUNT (CNTAMT) --<------- Note the parenthesis
    FROM TEMPTRAN
    WHERE authorised = ‘Y’
    GROUP BY LEDGERCD, SUBLEDCD, BRCD, VALUE_DATE, VCHCODE;
    

    (I assume that BALANCE, CNT, CNTAMT are columns in your TEMPTRAN table…)

  5. While creating materialized view on single table with the following conditions?
    I am getting error, any solutions?

    SQL>CREATE MATERIALIZED VIEW LOG ON TEMPTRAN WITH
    ROWID, (LEDGERCD, SUBLEDCD, BRCD, VALUE_dATE, VCHCODE)
    INCLUDING NEW VALUES;

    sql>CREATE MATERIALIZED VIEW MV_TEMPTRAN
    REFRESH FAST ON COMMIT
    AS
    SELECT LEDGERCD, SUBLEDCD, BRCD, VALUE_DATE, VCHCODE,
    SUM(VCH_AMT_MV) BALANCE, COUNT(*) CNT,
    COUNT(VCH_AMT_MV) CNTAMT
    FROM TEMPTRAN
    WHERE authorised = ‘Y’
    GROUP BY LEDGERCD, SUBLEDCD, BRCD, VALUE_DATE, VCHCODE;

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

  6. Ik onderscheid twee soorten beperkingsregels, die ieder op hun beurt weer verder onderverdeeld kunnen worden. Allereerst de regels die een niet-toegestane toestand (NTT) beschrijven en als tweede de regels die een niet-toegestane overgang (NTO) beschrijven.

    En niet-toegestane overgang is de combinatie van één toegestane begintoestand en één toegestane eindtoestand. Een toestand is een stelsel van feiten die op één moment in de tijd waar zijn.

    Een voorbeeld van een NTT:

    1. Persoon met sofi-nummer 1560.91.642 is geboren op 07-07-1970
    2. Persoon met sofi-nummer 1560.91.642 is geboren op 31-07-1968

    Deze twee zinnen kunnen niet tegelijkertijd waar zijn. Een persoon kan niet op twee verschillende tijdstippen geboren zijn. Daaruit kan men afleiden dat het sofi-nummer een unieke identificatie is voor een persoon. Dat resulteert in een primaire of alternatieve sleutel.

    Een voorbeeld van een NTO is :

    Begintoestand (TT1)
    1. Persoon met sofinr 1560.91.642 ontvangt een AOW uitkering

    Eindtoestand (TT2)
    2. Persoon met sofinr 1560.91.642 ontvangt een WW uitkering

    Een persoon mag geen WW uitkering ontvangen nadat hij/zij een AOW heeft ontvangen.

    De NTT kunnen in een relationele database worden gecontroleerd met SQL. In bovengenoemd geval betekent dat de vraag “Welke personen zijn geboren op meer dan één tijdstip� niet beantwoord mag worden, c.q. een lege verzameling moet zijn. Indien dat wel het geval is, krijgt de desbetreffende gebruiker in het gunstigste geval de melding welke (combinatie van) zinnen welke beperkingsregel overtreden.

    De meeste databases hebben de mogelijkheid bepaalde type beperkingsregels declaratief vast te leggen, dus zonder gebruik te maken van SQL.

    De NTO kunnen alleen met SQL worden gecontroleerd indien er historische informatie opgeslagen is, zodat men de feiten van de begintoestand kan reproduceren. In bovengenoemd geval betekent dat de feiten als volgt beschreven moeten worden:
    1. Persoon met sofinr 1560.91.642 ontvangt als eerste een AOW uitkering
    2. Persoon met sofinr 1560.91.642 ontvangt als tweede een WW uitkering

    en men dus een NTT beschrijft.

    Indien dit niet gewenst is, kan men dit met behulp van database triggers controleren.

    De diverse feiten worden achtereenvolgens bekend gemaakt aan de database. In het geval dat de zinnen in diverse administratiestructuren (tabellen) vastgelegd worden, kan de controle alleen achteraf, dus nadat alle relevante feiten bekend zijn gemaakt. Dat betekent dat er meerdere statements nodig zijn. Ik miste altijd de mogelijkheid zaken te controleren op transactie-niveau, zeg maar een PRE-COMMIT trigger. Voor de afleidingsregels (of rekenregels) mis ik nog een POST-COMMIT trigger.

    Door op deze creatieve wijze gebruik te maken van de mogelijkheden van de Oracle database, betekent dit dat elke beperkingsregel op deze wijze gecontroleerd kan worden. En zodoende de (niet officiële) PRE-COMMIT trigger is geboren.

    Ik mis inderdaad nog de mogelijkheid om de gebruiker mede te delen welke regels overtreden zijn en welke combinaties van zinnen hieraan ten grondslag liggen.

    De mooiste user interfaces sluiten overtredingen uit. De op één na mooiste user interfaces geven de gebruikers de mogelijkheid de algemene regel te kennen en correctief op te treden, dus de zinnen aan te passen die de regel overtreden.