Duke -- Thanks for the question regarding "SQL ASSERTIONS vs triggers, materialized views with constraints, etc", version 10.2.0

Submitted on 4-Mar-2008 8:32 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 4-Mar-2008 8:43

You Asked

I attended a Hotsos session by Toon Koppelaars yesterday morning on Semantic Query Optimization. Among other interesting topics, Toon lamented the lack of any DBMS implementing SQL "assertions". By which he meant a database-enforced constraint than encompassed more than a single column or a single tuple (record).

The example he gave was "a manager cannot manage more than 2 departments." One should be able to have a DDL statement "CREATE ASSERTION max_manager_departments AS CHECK... " containing some appropriate SQL statement. But of course no DBMS, including Oracle, allows such.

It seemed to me that these were the sorts of constraints that are usually implemented by the database designer in the form of triggers or materialized views with constraints. (Admittedly, as-implemented, most trigger-based constaints fail to account for Oracle's locking mechanisms, but that's an implementation issue). Here's an example of yours:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7144179386439

As a practical matter, are there any assertions that cannot be implemented via triggers or constrained materialized views? Or are there, ahem, "rules of thumb" or guidelines as to when one approach is better than another? It would seem to me that a discussion of "what we can implement (and this way is best)" and "what we can't implement" would be helpful.

Thanks!

and we said...

If assertions can all be answered by running some query against an "idle" (eg: no modifications in database) query - then I believe we can enforce them via a materialized view (refresh fast on commit), presuming the query is fast refreshable (meaning, the answer is probably "not all of them can be, some are not practical")

So, the "a manager cannot manage more than 2 departments" - easy with a fast refresh on commit materialized view.

Harder: a department must have at least one employee. That would be a (rare) case for a trigger - to maintain a count at the DEPT level (a multi-user safe trigger, it would serialize at the DEPTNO level on the updates to DEPT)

ops$tkyte%ORA10GR2> create table dept
  2  as
  3  select * from scott.dept;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table emp
  2  as
  3  select * from scott.emp;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table dept add constraint dept_pk primary key(deptno);

Table altered.

ops$tkyte%ORA10GR2> alter table emp add constraint emp_pk primary key(empno);

Table altered.

ops$tkyte%ORA10GR2> alter table emp add constraint emp_fk_dept foreign key(deptno) 
references dept(deptno);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into emp (empno,deptno) values ( 1234, 40 );

1 row created.

ops$tkyte%ORA10GR2> alter table dept add emp_cnt number default 0 not null;

Table altered.

ops$tkyte%ORA10GR2> update dept set emp_cnt = (select count(*) from emp where 
emp.deptno=dept.deptno);

4 rows updated.

ops$tkyte%ORA10GR2> alter table dept add constraint check_emp_count_gt_zero check 
(emp_cnt>0) deferrable initially deferred;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger emp_trigger after insert or update or 
delete
  2  on emp for each row
  3  begin
  4          if (inserting or (updating and :new.deptno <> :old.deptno))
  5          then
  6                  update dept set emp_cnt = emp_cnt+1 where deptno = :new.deptno;
  7          end if;
  8          if ((updating and :new.deptno <> :old.deptno) or deleting)
  9          then
 10                  update dept set emp_cnt = emp_cnt-1 where deptno = :old.deptno;
 11          end if;
 12  end;
 13  /

Trigger created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select deptno, emp_cnt from dept order by deptno;

    DEPTNO    EMP_CNT
---------- ----------
        10          3
        20          5
        30          6
        40          1

ops$tkyte%ORA10GR2> delete from emp where deptno = 20 and rownum = 1;

1 row deleted.

ops$tkyte%ORA10GR2> select deptno, emp_cnt from dept order by deptno;

    DEPTNO    EMP_CNT
---------- ----------
        10          3
        20          4
        30          6
        40          1

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> delete from emp where deptno = 40 and rownum = 1;

1 row deleted.

ops$tkyte%ORA10GR2> select deptno, emp_cnt from dept order by deptno;

    DEPTNO    EMP_CNT
---------- ----------
        10          3
        20          4
        30          6
        40          0

ops$tkyte%ORA10GR2> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.CHECK_EMP_COUNT_GT_ZERO) violated

would assertions be a "nice to have feature" - yes, they would...

Reviews

and deferrable constraints... thanks! March 4, 2008 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: Duke Ganote from HOTSOS 2008

Looking forward to seeing you Thursday!

Materialized View instead of Trigger March 4, 2008 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from UK

... and this is what happens when you try to avoid triggers (by adding constraints on materialized views)

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

scott@XE> create materialized view log on emp
  2  with rowid, (deptno)
  3  including new values;

Materialized view log created.

scott@XE> create materialized view dept_tot_mvw
  2  refresh fast on commit
  3  as select deptno, count(*) emp_cnt
  4  from emp
  5  group by deptno;

Materialized view created.

scott@XE> select deptno, emp_cnt from dept_tot_mvw order by deptno;

    DEPTNO    EMP_CNT                                                           
---------- ----------                                                           
        10          3                                                           
        20          5                                                           
        30          6                                                           

scott@XE> alter table dept_tot_mvw add constraint check_emp_count_gt_zero check (emp_cnt>0) 
deferrable initially deferred;

Table altered.

scott@XE> delete from emp where deptno = 10;

3 rows deleted.

scott@XE> commit;
ERROR:
ORA-03114: not connected to ORACLE 


commit
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error 

scott@XE> connect scott@xe
Connected.

scott@XE> select deptno, emp_cnt from dept_tot_mvw order by deptno;

    DEPTNO    EMP_CNT                                                           
---------- ----------                                                           
        20          5                                                           
        30          6                                                           

scott@XE>

it seems March 4, 2008 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

we need a
CREATE ASSERTION no_internal_oracle_error AS CHECK
oracle_implements_what_it_promises

March 4, 2008 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: David Aldridge from Fairfax, VA

It looks to me like this:

alter table dept_tot_mvw add constraint check_emp_count_gt_zero check (emp_cnt>0) 


... is not going to work as intended anyway ("all departments must have an employee"?). You'll need 
a different, possibly non-fast refreshable, MV definition to do that.

My guess though is that the contraint is raising an internal error based on an intermediate result 
set in the recursive sql that maintains the mv data. The delete clause is not available in MERGE 
until 10g, and might not even be implemented in MV refresh (a sql trace would tell you).

What if you check (emp_cnt>1) and just delete one of those rows? Does that work?

For Duke Ganote March 5, 2008 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: SeánMacGC from Ireland

You may be interested in checking out this book from Toon and Lex de Haan (RIP), if he didn't mention it at your Hotsos session:

http://www.amazon.com/Applied-Mathematics-Database-Professionals-Experts/dp/1590597451/ref=sr_1_1?ie
=UTF8&s=books&qid=1204710513&sr=8-1

Goes into the whole area of the declarative constraint deficiencies of current DBMS products in some detail, including practical workarounds for some of those same deficiencies.

sql assertions - in the future ? March 5, 2008 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: Peter from Denmark

Does anybody know whether Oracle or any other database vendor are planning - or even considering - 
future support for SQL ASSERTIONS ? Would it be very difficult to implement ? Is it just not 
considered relevant/useful by the database product designers ?

Peter from Denmark March 5, 2008 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: SeánMacGC from Ireland

The implementation of ANSI-SQL ASSERTIONS in full would not be that 'easy', hence the reluctance of the vendors to adopt them thus far despite their undoubted high-value. I suspect they will resist their introduction until such times as the clamouring from the user community is more hassle than it's worth, to the point of unbearability, however long away that may be.

to SeánMacGC March 6, 2008 - 12am Central time zone

Bookmark | Bottom | Top

Reviewer: Duke Ganote from looking forward to HOTSOS training day 2008

Thanks!  He did not mention it, but it's already on my wishlist (in a few months my family'll be 
singing "happy birthday to the dweeb")!

The book... March 6, 2008 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: Toon Koppelaars from Dallas

A few comments for this thread.

1) I did mention the AM4DP book, however not in relation with ASSERTIONS, but in relation with the 
presentation subject at hand, which was semantic query optimization.
2) Chapter 11 goes into detail of how difficult it is to support (efficient) CREATE ASSERTION 
declaratively.
3) Any DBMS vendor can probably give us an implementation of CREATE ASSERTION within a couple of 
weeks, however the implementation details would be such that you and I would never use it in our 
high transaction, performance critical environments. The crux is in getting an efficient 
implementation, and again I explain the difficulties in this area in Chapter 11.
Think of it as this. The foreign key has been implemented very efficiently, we all use it, hardly 
gives any performance or serialization issues. The vendor has embedded dedicated 
'foreign-key-checking' code into its DBMS for this. Now for CREATE ASSERTION, the vendor has to 
code something far, far more difficult: it has to develop generic code that can take an assertion 
(ie. any multi-table query) and derive from that an implementation that, a) performs well, and b) 
imposes the least restrictions possible in the area of concurrency.
This is hard. And also, I believe, not fully scientifically researched yet.


Toon

PS. By the way the book has sold out. My publisher informed me yesterday that it will be available 
again in a couple of weeks.

ASSERTIONs Complexity March 7, 2008 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: SeánMacGC from Ireland

Thanks for that Toon, and certainly the science around ASSERTIONs needs no little effort.

Would it be fair to say that, paradoxically, it would seem to me, that the less evolved a vendor locking/concurrency architecture, the 'easier' it would be to implement ASSERTIONs. SQL Server (SQL Server 2005 Snapshot mode notwithstanding), Teradata, etc., are already throttling concurrency and performance where reads blocking writes and writes blocking reads are concerned (Read Uncommitted doesn't count at all, and should be outlawed ;o) ). Therefore, the incorporation of a further level of constraint enforcement, albeit at a much more complex level of data integrity constraining, should, theoretically, be not much more perceptible to the already grossly perceptible lock-outs for the user?

Oracle, on the other hand, with its (most) highly evolved and fastidously unobtrusive locking/concurrency architecture, would be faced with the greatest of challenges, in order to maintain performance. And the challenges therein are indeed very considerable.

March 7, 2008 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: Toon Koppelaars

Sean, No I would disagree with your hypothesis (the more evolved concurrenty-model implies the more 
difficult to deal with locking issues around assertions).
The two are somewhat related, but also independent, in terms of what they both (standard locking 
done by DBMS, and additional locking required for managing integrity constraints) try to achieve. 
I'd need some time to do a write-up that tries to explain this, though (not now).

Toon

March 7, 2008 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: SeánMacGC from Ireland

Thanks for that Toon, appreciate the distinction between standard locking and constraint locking, 
and I look forward to your paper.

MV and ORA-00603 May 9, 2009 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Vlado from Knoxville, TN

If you specify the constraint on the mview as initially immediate then you won't encounter the 
ORA-00603.

Following is a simplfied example, first showing the behaviour with the initially deferrable and 
then with initially immediate.

21:16:15 SQL>select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.15
21:16:15 SQL>
21:16:15 SQL>create table emp (deptno int, empno int primary key);

Table created.

Elapsed: 00:00:00.37
21:16:16 SQL>create materialized view log on emp with rowid, (deptno) including new values;

Materialized view log created.

Elapsed: 00:00:00.40
21:16:16 SQL>
21:16:16 SQL>
21:16:16 SQL>insert into emp (empno,deptno) values ( 1234, 40 );

1 row created.

Elapsed: 00:00:00.12
21:16:16 SQL>insert into emp (empno,deptno) values ( 1235, 40 );

1 row created.

Elapsed: 00:00:00.09
21:16:16 SQL>
21:16:16 SQL>commit;

Commit complete.

Elapsed: 00:00:00.09
21:16:16 SQL>
21:16:16 SQL>
21:16:16 SQL>create materialized view dept_tot_mvw
21:16:16   2  refresh fast on commit
21:16:16   3  as select deptno, count(*) emp_cnt
21:16:16   4  from emp
21:16:16   5  group by deptno;

Materialized view created.

Elapsed: 00:00:00.37
21:16:17 SQL>
21:16:17 SQL>alter table dept_tot_mvw add constraint check_emp_count_gt_zero check (emp_cnt>1) 
deferrable initially deferred;

Table altered.

Elapsed: 00:00:00.12
21:16:17 SQL>
21:16:17 SQL>select * from dept_tot_mvw;

    DEPTNO    EMP_CNT
---------- ----------
        40          2

Elapsed: 00:00:00.17
21:16:17 SQL>
21:16:17 SQL>delete emp where deptno=40 and empno=1234;

1 row deleted.

Elapsed: 00:00:00.11
21:16:17 SQL>
21:16:17 SQL>commit;
ERROR:
ORA-03114: not connected to ORACLE


commit
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
Process ID: 0
Session ID: 1627 Serial number: 18906


Elapsed: 00:00:02.62
21:16:20 SQL>


21:16:21 SQL>connect vbarun@xxxx
Enter password:
Connected.
21:16:38 SQL>insert into emp (empno,deptno) values ( 1234, 40 );

1 row created.

Elapsed: 00:00:00.11
21:16:38 SQL>
21:16:38 SQL>commit;

Commit complete.

Elapsed: 00:00:00.31
21:16:39 SQL>
21:16:39 SQL>select * from dept_tot_mvw;

    DEPTNO    EMP_CNT
---------- ----------
        40          2

Elapsed: 00:00:00.17
21:16:39 SQL>
21:16:39 SQL>alter table dept_tot_mvw drop constraint check_emp_count_gt_zero ;

Table altered.

Elapsed: 00:00:00.14
21:16:39 SQL>
21:16:39 SQL>alter table dept_tot_mvw add constraint check_emp_count_gt_zero check (emp_cnt>1) 
deferrable initially immediate;

Table altered.

Elapsed: 00:00:00.14
21:16:39 SQL>
21:16:39 SQL>delete emp where deptno=40 and empno=1234;

1 row deleted.

Elapsed: 00:00:00.12
21:16:39 SQL>
21:16:39 SQL>commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (VBARUN.CHECK_EMP_COUNT_GT_ZERO) violated


Elapsed: 00:00:00.43

No ORA-00603 in 11g May 9, 2009 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Vlado from Knoxville, TN

In 11g, instead of a ora-00603 you get a ORA-12008 and ORA-00600, and the delete statement does not 
succeed even with immediate deferred!

21:20:53 SQL>select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.18
21:20:53 SQL>
21:20:53 SQL>create table emp (deptno int, empno int primary key);

Table created.

Elapsed: 00:00:00.14
21:20:54 SQL>create materialized view log on emp with rowid, (deptno) including new values;

Materialized view log created.

Elapsed: 00:00:00.15
21:20:54 SQL>
21:20:54 SQL>
21:20:54 SQL>insert into emp (empno,deptno) values ( 1234, 40 );

1 row created.

Elapsed: 00:00:00.12
21:20:54 SQL>insert into emp (empno,deptno) values ( 1235, 40 );

1 row created.

Elapsed: 00:00:00.14
21:20:54 SQL>
21:20:54 SQL>commit;

Commit complete.

Elapsed: 00:00:00.12
21:20:54 SQL>
21:20:54 SQL>
21:20:54 SQL>create materialized view dept_tot_mvw
21:20:54   2  refresh fast on commit
21:20:54   3  as select deptno, count(*) emp_cnt
21:20:54   4  from emp
21:20:54   5  group by deptno;

Materialized view created.

Elapsed: 00:00:00.50
21:20:55 SQL>
21:20:55 SQL>alter table dept_tot_mvw add constraint check_emp_count_gt_zero check (emp_cnt>1) 
deferrable initially deferred;

Table altered.

Elapsed: 00:00:00.12
21:20:55 SQL>
21:20:55 SQL>select * from dept_tot_mvw;

    DEPTNO    EMP_CNT
---------- ----------
        40          2

Elapsed: 00:00:00.17
21:20:55 SQL>
21:20:55 SQL>delete emp where deptno=40 and empno=1234;

1 row deleted.

Elapsed: 00:00:00.10
21:20:55 SQL>
21:20:55 SQL>commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00600: internal error code, arguments: [ktcccadd-0], [], [], [], [], [], [], []


Elapsed: 00:00:02.89
21:20:58 SQL>
21:20:58 SQL>
21:20:58 SQL>
21:20:58 SQL>insert into emp (empno,deptno) values ( 1234, 40 );
insert into emp (empno,deptno) values ( 1234, 40 )
*
ERROR at line 1:
ORA-00001: unique constraint (VBARUN.SYS_C0055427) violated


Elapsed: 00:00:00.54
21:20:58 SQL>
21:20:58 SQL>commit;

Commit complete.

Elapsed: 00:00:00.10
21:20:59 SQL>
21:20:59 SQL>select * from dept_tot_mvw;

    DEPTNO    EMP_CNT
---------- ----------
        40          2

Elapsed: 00:00:00.17
21:20:59 SQL>
21:20:59 SQL>alter table dept_tot_mvw drop constraint check_emp_count_gt_zero ;

Table altered.

Elapsed: 00:00:00.14
21:20:59 SQL>
21:20:59 SQL>alter table dept_tot_mvw add constraint check_emp_count_gt_zero check (emp_cnt>1) 
deferrable initially immediate;

Table altered.

Elapsed: 00:00:00.15
21:20:59 SQL>
21:20:59 SQL>delete emp where deptno=40 and empno=1234;

1 row deleted.

Elapsed: 00:00:00.11
21:20:59 SQL>
21:20:59 SQL>commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (VBARUN.CHECK_EMP_COUNT_GT_ZERO) violated


Elapsed: 00:00:00.42
21:21:00 SQL>

Write a Review