Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Materialize View Constraints

Expert Oracle Tips by Laurent Schneider

June 8, 2011


Materialized View Constraints

Oracle is pretty efficient at enforcing constraints.

Table t is created for this exercise:

create table t(x number primary key, y number);

For instance if you
alter table t add check (y<1000); then Y will not be bigger than 1000, right?

SQL> insert into t values (1,2000);
insert into t values (1,2000)
Error at line 1
ORA-02290: check constraint (SCOTT.SYS_C0029609) violated

I believe this code to be unbreakable. If you have only SELECT and INSERT privilege on the table, you cannot bypass the constraint.

Let�s imagine some complex constraint:  CHECK (sum(y) < 1000)

SQL> alter table t add check (sum(y) < 1000);
alter table t add check (sum(y) < 1000)
Error at line 1
ORA-00934: group function is not allowed here

Ok, the message from Oracle is clear enough.  We cannot handle this complex constraint with a CHECK condition.

We could have some trigger that fires before an exception happens:

   ON T
   WHEN (NEW.Y > 0)
   s   NUMBER;

   IF (s + :new.y >= 1000)
      raise_application_error (-20001, 'SUM(Y) would exceed 1000');
   END IF;

Now the trigger will compute the sum and return an exception whenever it fails.

SQL> insert into t values (2, 600);

1 row created.

SQL> insert into t values (3, 600);
insert into t values (3, 600)
ERROR at line 1:
ORA-20001: SUM(Y) would exceed 1000
ORA-06512: at "SCOTT.TR", line 8
ORA-04088: error during execution of trigger 'SCOTT.TR'

SQL> drop trigger tr;

Trigger dropped.

SQL> truncate table t;

Table truncated.

I am not good with triggers. And then again, triggers are as bad as their developers and have dark sides like mutating triggers and the like.

The code above is not efficient if more than one user updates the table at the same time

Another popular approach is to create a fast-refreshable-on-commit mview with a constraint.

Let�s see how this works.

create materialized view log on t with rowid, primary key (y) including new values;

create materialized view mv
refresh fast
on commit
as select sum(y) sum from t;

alter table mv add check (sum < 1000);

The constraint is on the mview, so once you commit (and only at commit time), Oracle will try to refresh the mview.

SQL> insert into t values (4, 600);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values (5, 600);

1 row created.

SQL> commit;
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.SYS_C0029631) violated

SQL> select * from t;

         X          Y
---------- ----------
         4        600

So far so good. The mechanism rollbacks the transaction in case of an ORA-12008. This is bit similar to a DEFERRABLE constraint.

But how safe is this after all? Oracle does not enforce anything on the table, it just fails on refresh�

Anything that does not fulfill the materialized view fast refresh requisites will also break the data integrity.

SQL> delete from t;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert /*+PARALLEL*/ into t select 100+rownum, rownum*100 from dual connect by level<20;

19 rows created.

SQL> commit;

Commit complete.

SQL> select sum(y) from t;


SQL> select staleness from user_mviews;


Your data integrity is gone!

 By �breaking� the mview, with only SELECT, INSERT and ALTER SESSION privilege, you can now insert any data.

This is documented as a FAST Clause.

For both conventional DML changes and for direct-path INSERT operations, other conditions may restrict the eligibility of a materialized view for fast refresh.

Other operations like TRUNCATE may also prevent the insertion of fresh data

SQL> alter materialized view mv compile;

Materialized view altered.

SQL> exec dbms_mview.refresh('MV','COMPLETE');

PL/SQL procedure successfully completed.

SQL> select * from mv;


SQL> insert into t values(1,1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv; 


SQL> truncate table t;

Table truncated.

SQL> insert into t values(1,1);

1 row created.

SQL> commit;
ERROR at line 1:
ORA-32321: REFRESH FAST of "SCOTT"."MV" unsupported after detail table


Get the Complete
Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,150 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks


Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA