and deferrable constraints... thanks!
Duke Ganote, March 04, 2008 - 8:59 am UTC
Looking forward to seeing you Thursday!
Materialized View instead of Trigger
A reader, March 04, 2008 - 10:23 am UTC
... 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
A reader, March 04, 2008 - 10:34 am UTC
we need a
CREATE ASSERTION no_internal_oracle_error AS CHECK
oracle_implements_what_it_promises
David Aldridge, March 04, 2008 - 5:18 pm UTC
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
SeánMacGC, March 05, 2008 - 5:03 am UTC
sql assertions - in the future ?
Peter, March 05, 2008 - 6:25 am UTC
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
SeánMacGC, March 05, 2008 - 12:02 pm UTC
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
Duke Ganote, March 06, 2008 - 12:31 am UTC
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...
Toon Koppelaars, March 06, 2008 - 6:28 am UTC
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
SeánMacGC, March 07, 2008 - 4:01 am UTC
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.
Toon Koppelaars, March 07, 2008 - 7:32 am UTC
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
SeánMacGC, March 07, 2008 - 9:05 am UTC
Thanks for that Toon, appreciate the distinction between standard locking and constraint locking, and I look forward to your paper.
MV and ORA-00603
Vlado, May 09, 2009 - 9:39 pm UTC
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
Vlado, May 09, 2009 - 9:44 pm UTC
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>