Materialized View Constraints SQL Tips by Burleson Consulting

Base relations – tables – and derived ones – views – are all fundamental building blocks of Relational databases. A derived relation may be virtual, meaning the defining relational expression is evaluated in terms of the base relations; or materialized, meaning the relation is actually stored.  In database practice they are commonly referred to as plain views and materialized views, correspondingly.

In many respects a materialized view is similar to a base table. One can index it, declare a constraint, even (heaven forbid) associate a trigger. Declaring constraints upon materialized views turns out to be a very powerful method of enforcing complex constraints with limited SQL support.

In the database, research literature leveraging materialized views for constraint enforcement has been suggested as early as in 1978-1979. Given that it was a long time before materialized views became a reality in practical RDBMS implementations, those ideas had to remain dormant.  It was Tony Andrews who sparked a renewed interest in materialized view constraint enforcement in the Oracle community.

When declaring a constraint, the starting point is expressing it as a formal expression. Lets start analyzing how to represent the foreign key integrity constraint via materialized views. Please note carefully that we do this not because contemporary RDBMS engines lack foreign key support, or their implementation is deficient in any way. We do it solely because foreign key constraint is conceptually simple and yet semantically rich example which will provide a basis to further advance our technique. So, in plain English:

For any record in the Emp table there has to be a matching Dept record.

This constraint declaration is informal because of the term matching.

A more precise statement would read:

For any record e in the Emp table there has to exist a Dept d such that e.deptno = d.deptno.

It is generally a good idea to rephrase a constraint as an impossible condition. This does not really change anything from a logical perspective, but in real life, law enforcement implies some real world action, which has to be invoked whenever the law is broken. For example:

There doesn’t exist a record e in the Emp table such that there is no Dept d  such that e.deptno = d.deptno.

Admittedly, this sentence sounds awful (even for me, who wrote it in the first place). I can imagine how a reader without background in logic might feel. What is the point of elaborating formal content if at the end all that is produced is such gibberish as the last statement?

Well, this situation is common in math. An expression is transformed through a series of steps, so the expression might become ugly in the process, but sometimes luck gets involved and it finally collapses into a simple formula. In this example, it takes one more step

Consider all the record e in the Emp table such that there is no Dept d with e.deptno = d.deptno. There mustn’t to be any!

before the statement finally contracts to:

The difference between the set of the Emp.dept and Dept.dept is empty.

It is formal, because it can be expressed in SQL, where the only missing part is equality between views:

select deptno from Emp
minus
select deptno from Dept
= ∅

Let’s refer to the view on the left side as EmpWithoutDept.

On afterthought, this assertion is obvious. The Emp-Dept referential integrity constraint is violated only if the EmpWithoutDept view becomes nonempty. It could happen when a tuple is inserted into the Emp table, or deleted from the Dept table.

Since there is no concept of equality between views in SQL, the condition for emptiness must be expressed by other means. If the EmpWithoutDept view is not empty, then EmpWithoutDept.dept is not NULL! A simple check constraint would do:

CREATE MATERIALIZED VIEW EmpWithoutDept AS
select deptno from Emp
minus
select deptno from Dept; 

ALTER TABLE emp_minus_dept_mv
ADD CONSTRAINT EWD_is_empty CHECK( deptno is null );

Whenever a referential integrity constraint is violated, the EmpWithoutDept view becomes nonempty triggering a violation of the EWD_is_empty constraint.

While plain views do not require any maintenance, materialized views need to be kept up to date with the base relations. Efficient update of materialized views is achieved via incremental evaluation.

In practice we have to deal with database implementations which dictate seemingly arbitrary limitations on what operations do support incremental refresh and which ones do not. Oracle circa 2005 does not support set operators in the definition of incrementally maintained materialized views, for example. There is a workaround this limitation, though. The assertion could be rewritten into an equivalent form that leverages only supported operators:

CREATE MATERIALIZED VIEW EmpOuterJoinDept
REFRESH FAST ON COMMIT AS
select d.deptno ddept, d.rowid drid, e.rowid erid
from Emp e, Dept d
where e.deptno=d.deptno(+); 

ALTER TABLE EmpOuterJoinDept
ADD CONSTRAINT ck_oj_mv CHECK(ddept is not null);

However, one especially attractive feature is lost: this materialized view is no longer empty.

In general, constructing incrementally updateable materialized views with such limitations becomes a coding exercise that tests one’s patience.  It is almost always possible to express an operator in a chain of incrementally refreshable materialized views. Here is how the minus operator can be represented:

create materialized view empDepts as
select deptno, count(*) cnt
from emp
group by deptno; 

create materialized view deptEmpCross as
select dept.deptno dd, empDepts.deptno ed, dept.rowid drid, empDepts.rowid erid
from empDepts, dept; 

create materialized view deptEmpJoin as
select dept.deptno dd, empDepts.deptno ed, dept.rowid drid, empDepts.rowid erid
from empDepts, dept
where dept.deptno=empDepts.deptno;

create materialized view deptEmpUnion as
select '1' marker,dd,rowid rid
from deptEmpCross
union all
select '2' marker,dd,rowid rid
from deptEmpJoin;

create materialized view deptCounts as
select dd, count(*) c
from deptEmpUnion
group by dd; 

create materialized view empDeptCount as
select count(*) c
from empDepts; 

create materialized view Final as
select dd, c1.rowid rid1, c2.rowid rid2
from empDeptCount c1, deptCounts c2
where c1.c=c2.c;

The basic idea is that a set difference can be expressed via joins and aggregation with counting, and an incremental refresh of the latter operations is supported. This is pointless from a practical perspective, however. It is hard to imagine that anybody would buy the overhead of 7 (!) nonempty materialized views for a mere set difference implementation.

Sooner or later incremental refresh limitations will be lifted. For the purpose of further constraint study in this book, let’s continue pretending as if it already happened.