Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Materialized View Constraints

SQL Tips by Donald Burleson

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

select deptno from Emp
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.

Incremental Evaluation

Queries and updates often do not get along with each other. They are conflicting goals from a performance perspective. We can speed up some queries at the cost of introducing some auxiliary structures. The most familiar examples of such structures are indexes and materialized views, and there are other cases which warrant a general concept.

Those structures need to be kept up to date with the base tables. Completely reevaluating them is out of question for any sizeable database. They have to be maintained incrementally: a change to the structure is small when the update transaction is small, which makes the overall performance acceptable. Incremental query evaluation is one of the most important performance ideas in the database world.

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:

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.

Trigger Solution is Unreliable

A comparison between the materialized view and trigger based solution does not favor the latter. First, the trigger has to cover all operations: insert, update and delete - missing any of them would allow integrity violation. Second, a constraint involving more than one table has to be covered by multiple triggers as in the example on both Emp and Dept tables.

More important, however, is that writing triggers is a challenging exercise from concurrency semantics perspective. It is better to delegate complex code to RDBMS engine developers, and leverage the high level features that RDBMS offers.

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. 


This is an excerpt from the new book SQL Design Patterns: The Expert Guide to SQL Programming by Vadim Tropashko

You can buy it direct from the publisher for 30%-off.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.