Materialize View Constraints

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

CREATE TRIGGER tr
   BEFORE INSERT OR UPDATE
   ON T
   FOR EACH ROW
   WHEN (NEW.Y > 0)
DECLARE
   s   NUMBER;
BEGIN
   SELECT SUM (y) INTO s FROM t;

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

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.

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;

    SUM(Y)
----------
     19000

SQL> select staleness from user_mviews;

STALENESS
-------------------
UNUSABLE

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;

       SUM
----------

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv; 

      SUM
----------
         1

SQL> truncate table t;

Table truncated.

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

1 row created.

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