0 
0 Put on the trail by Tom Kyte – and after many useful discussions with my colleague Andre, we will take a look in this article at implementing complex business rules declaratively using Materialized Views. A previous post – Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines) – describes how to implement the mandatory master-detail relation (from the Master’s point of view) using Materialized Views.
In this post we will look at several other types of rules:
- Master must have a restricted number of details (no fewer than X and not more than Y) – for example: A Department must have between 2 and 6 employees (or Clerks)
- The Sum of a certain Detail Column is restricted within a master – for example: the Salary Sum per Department may not exceed 20000
- The start-date and end-date of detail-records must be between the Master’s Startdate and Enddate – for example: Project Assignments must not begin before the Project’s startdate or end after the Project’s enddate.
You will see below how each of these rule categories can be implemented declaratively.
Master must have a restricted number of details
We will use EMP and DEPT as our example system. We want to enforce the rule that a Department must have between 2 and 6 employees. Note: we cannot enforce in this way that the department must have at least one employee; for that Mandatory Master-Detail rule, you should look at the earlier post:Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines).
We must make sure that we have the proper constraints in order to create the proper Materialized View logs that will allow Fast Refresh (on commit):
alter table emp add constraint emp_pk primary key(empno); alter table dept add constraint dept_pk primary key(deptno); create materialized view log on emp with rowid(deptno) including new values; create materialized view log on dept with rowid including new values;
Now that these preprarations are done, we can create the Materialized View itself:
create materialized view emp_mv refresh fast on commit as select deptno, count(*) cnt from emp group by deptno;
Let us test this Materialized View – that is refreshed on commit:
select * from emp_mv
/
DEPTNO CNT
---------- ----------
10 3
20 5
30 6Whenever we perform data manipulation on EMP, this Materialized View will be refreshed (or synchronized) with the changes as soon as we commit.
Now we add a Check Constraint on the Materialized View, restricting the value of the CNT column:
alter table emp_mv add constraint check_cnt check ( cnt between 2 and 6 ) deferrable;
This specifies that CNT must be between 2 and 6 and that in turn restricts DML on EMP: no insert/update/delete on EMP may lead to a record in EMP_MV where CNT is not between 2 and 6. We can not simply remove Employees from a certain Department or add Employees to a Department:
insert into emp (empno, sal, deptno) values (1111,90,30) / 1 row created. SQL> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (SCOTT.CHECK_CNT) violated
The Sum of a certain Detail Column is restricted within a master
We will use EMP and DEPT as our example system. We want to enforce the rule that the sum of salaries in a Department must be smaller than 20000.
create materialized view log on emp WITH SEQUENCE , PRIMARY KEY, ROWID (deptno,job,sal) including new values;
create materialized view emp_deptsalsum_mv
refresh fast on commit
as
select deptno
, sum(sal) sumsal
, count(*)
, count(sal) head_count
from emp
group
by deptno
/
select * from emp_deptsalsum_mv
/
DEPTNO SUMSAL COUNT(*) HEAD_COUNT
---------- ---------- ---------- ----------
10 13126.5 3 3
20 11101 5 5
30 10451 6 6Now we add the constraint that will put a cap on the sum of the salaries per department:
alter table emp_deptsalsum_mv add constraint check_dept_salsum check ( sumsal < 20000) deferrable / update emp set sal = sal* 2 / commit / commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (SCOTT.CHECK_DEPT_SALSUM) violated
It is clear that in this way we have put our business rule in place: any manipulation of the salaries in EMP is at commit time propagated in aggregated form in the Materialized View where it is subsequently subjugated to the check constraint.
The start-date and end-date of detail-records must be between the Master's Startdate and Enddate
Here we make use of another demonstration application with tables Projects and ProjectAssignments Our rule in this particular case will be: Project Assignments must not begin before the Project's startdate or end after the Project's enddate. The tables we will work with:
create table project ( projno int primary key , start_date date not null , end_date date not null ) / create table emp_proj_assign ( empno int not null , projno int not null , start_date date not null , end_date date not null , primary key (empno, start_date) ) /
We know now that we will need Materialized View Logs in order to achieve fast refresh on commit, so here we go:
create materialized view log on project with rowid(projno,start_date,end_date) including new values / create materialized view log on emp_proj_assign with rowid(projno,start_date,end_date) including new values/ /
The Materialized View used for this situation selects each record from the ProjectAssignments table, joined to its parent Project record. It looks as follows:
create materialized view emp_proj_mv refresh fast on commit as select ep.projno , ep.start_date ep_start_date , ep.end_date ep_end_date , p.start_date p_start_date , p.end_date p_end_date , ep.rowid ep_rowid , p.rowid p_rowid from emp_proj_assign ep, project p where ep.projno = p.projno /
The check we need to enforce that the project assignments fall within the limits set by the project’s start_date and end_date now becomes very simple:
alter table emp_proj_mv add constraint emp_proj_mv_chk1 check (ep_start_date >= p_start_date and ep_end_date < = p_end_date) deferrable;
Let's now test our new business rule:
insert into project values (1,'01-jan-2004','31-jan-2004');
insert into emp_proj_assign values (1,1,'01-jan-2004','31-jan-2004');
commit;
select *
from emp_proj_mv
/
PROJNO EP_START_ EP_END_DA P_START_D P_END_DAT EP_ROWID
---------- --------- --------- --------- --------- ------------------
P_ROWID
------------------
1 01-JAN-04 31-JAN-04 01-JAN-04 31-JAN-04 AAAUYZAAEAAAHzvAAA
AAAUYXAAEAAAHzfAAASo far so good. I will now try to circumvent the business rule by inserting an assignment that is way outside the project’s timescope:
insert into emp_proj_assign values (1,1,'02-jan-2004','31-may-2004'); commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (SCOTT.EMP_PROJ_MV_CHK1) violated
It appears we have plugged this hole too.
You might object to the fact that we create redundant datastorage just for implementing a business rule. We are in fact currently duplicating data in the Materialized View for no other reason than enforcing a piece of business logic. Well, we can make it leaner by adding a where-condition to the definition of the Materialized: we only need to have the records in the MV that violate the check constraint; the others are completely superfluous
drop materialized view emp_proj_mv / create materialized view emp_proj_mv refresh fast on commit as select ep.projno , ep.start_date ep_start_date , ep.end_date ep_end_date , p.start_date p_start_date , p.end_date p_end_date , ep.rowid ep_rowid , p.rowid p_rowid from emp_proj_assign ep, project p where ep.projno = p.projno and not (ep.start_date <= p.start_date and ep.end_date < = p.end_date) -- NEW CONDITION / alter table emp_proj_mv add constraint emp_proj_mv_chk1 check (ep_start_date <= p_start_date and ep_end_date < = p_end_date) deferrable /
Now we achieve the same check, without ever actually loading any data in the MV.
Having come this far, we can further simplify the Materialized View:
drop materialized view emp_proj_mv / create materialized view emp_proj_mv refresh force on commit as select 1 dummy from emp_proj_assign ep, project p where ep.projno = p.projno and (ep.start_date < p.start_date or ep.end_date > p.end_date) / alter table emp_proj_mv add constraint emp_proj_mv2_chk check (1=0) deferrable /
Combining Mandatory Master Detail and Restricted Aggregate
I have made an attempt to really implement the rule that the number of Employees in a Department must be between 2 and 6 in a single Materialized View – including the requirement that each Department may not have zero Employees. We have to take into account that a MV on a join with aggregation does not allow Fast Refresh On Commit – just like that. We will use a double layered construction to work around that limitation:
create materialized view log on temp.emp WITH SEQUENCE , PRIMARY KEY, ROWID (deptno,job,sal) including new values / create materialized view log on temp.dept with SEQUENCE , rowid (deptno) including new values / create materialized view emp_dept_mv refresh force on commit as select nvl(e.rowid, d.rowid) pk_rowid , e.rowid emp_rowid , d.rowid dept_rowid , e.empno, d.deptno , e.job , e.sal from emp e , dept d where e.deptno(+) = d.deptno / alter table emp_dept_mv add constraint emp_dept_pk primary key(pk_rowid) / create materialized view log on emp_dept_mv with rowid (pk_rowid, emp_rowid, dept_rowid, empno, deptno, job, sal) including new values / create materialized view emp_deptsalsum_mv refresh force on commit as select deptno , sum(sal) sumsal , count(*) , count(sal) head_count from emp_dept_mv group by deptno / alter table emp_deptsalsum_mv add constraint check_dept_count check ( head_count between 2 and 7) deferrable / insert into dept (deptno, dname) values (50, 'CATERING') / pause commit / commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (SCOTT.check_dept_count) violated
To really show off, we can now make the Business Rule dynamic or context sensitive: let’s specify the maximum headcount per department:
alter table dept add ( max_headcount integer) / update dept set max_headcount = 8 / update dept set max_headcount = 4 where loc = 'BOSTON' /
So now the column max_headcount in the DEPT table will specify for each Department what its maximum headcount it. Of course we want to enforce that business rule.
drop materialized view emp_dept_mv / create materialized view emp_dept_mv refresh force on commit as select nvl(e.rowid, d.rowid) pk_rowid , e.rowid emp_rowid , d.rowid dept_rowid , e.empno , d.deptno , d.max_headcount , e.job, e.sal from emp e , dept d where e.deptno(+) = d.deptno / alter table emp_dept_mv add constraint emp_dept_pk primary key(pk_rowid) / create materialized view log on emp_dept_mv with rowid (pk_rowid, emp_rowid, dept_rowid, empno, deptno,max_headcount, job, sal) including new values / drop materialized view emp_deptsalsum_mv / create materialized view emp_deptsalsum_mv refresh force on commit as select deptno , sum(sal) sumsal , count(*) , count(sal) head_count , max_headcount from emp_dept_mv group by deptno , max_headcount / alter table emp_deptsalsum_mv add constraint check_dept_count check ( head_count between 1 and max_headcount) deferrable / update emp set deptno = 40 where deptno = 20 and rownum < 5 / select d.deptno , d.max_headcount , count(empno) headcount from emp e , dept d where e.deptno(+) = d.deptno group by d.deptno, max_headcount / commit /
Some notes on Materialized Views
You can define triggers on a Materialized View although strictly speaking it is apparently not supported. Trying to perform DML from a trigger made things quite tricky, as this DML is performed while the session is in the Commit phase of a transaction. When I tried to update a table on which I had defined a deferred check constraint in such a way that this constraint was violated, I ran into an Internal Server Error!
One of my ambitions with implementing business rules is to present the application and eventually the end user a complete list of all violations of business rule instead of just giving up on the first error. The way we have implemented the business rules above is very straighforward: the first violation will return an exception to the user with no indication whatsoever what else may be wrong - or even of which record violated the rule.
By using a Before Row trigger on the Materialized View, I can at least identify the record violating the Business Rule:
create or replace trigger pat_br
before delete or insert or update on emp_proj_mv
for each row
begin
register_br_violation( br=> 'emp_proj_mv2_chk' , assignment=> :new.ep_rowid);
end;
/
create or replace trigger pat_as
after delete or insert or update on emp_proj_mv
begin
if any_violations
then
insert into raise_exception values (1);
end if;
end;
/Now of course we have to remove the check constraint since otherwise we would never get past the first record -although at least this time we would know which record violates the business rule. I have set up a table raise_exception:
create table raise_exception ( a int primary key ) / create sequence re_seq / create or replace trigger re_br before insert on raise_exception for each row begin select re_seq.nextval into :new.a from dual ; end; / alter table raise_exception add constraint re_general_exception check( a is null) deferrable initially deferred /
I hoped that because of the deferred check constraint on raise_exception, I can have multiple Materialized Views record their exceptions in this table where the deferred check constraint would only kick in at the very end of the transaction after all Materialized Views have been refreshed and therefore all potential violations of business rules were spotted. Unfortunately, this does not happen. In fact, the deferred check constraint on raise_exception causes severe problems.
commit; ERROR: ORA-03114: not connected to ORACLE commit * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error
So far for trying to be very clever.
Another approach with the table raise_exception did not work either – the Materialized View created below did not refresh on commit:
create materialized view log on raise_exception with primary key, rowid including new values / drop materialized view re_mv / create materialized view re_mv refresh fast on commit as select count(*) c from raise_exception / alter table re_mvadd constraint re_general_exception check( c = 0) deferrable initially deferred /
Resources
Data Warehousing: Room with a Better View, Oracle Magazine, March/April 2005 by Arup Nanda – an interesting introduction to Materialized Views in Oracle 10g – basic intro on Query Rewrite, the dbms_mview package and the related Hint – /*+ REWRITE_OR_ERROR */.
AskTom: Declarative IntegrityAs an advocate of enforcing as much integrity as possible declaratively, I am disappointed that little has changed in that arena in 10G. CHECK constraints are still limited to simple single-table, single-row checks, and triggers have to be used to enforce any more complex rules. Do you think Oracle will ever add multi-table check constraints or ASSERTIONS as defined by ANSI SQL? Or has declarative integrity gone about as far as it is going to in Oracle?
AskTom: Create Materialized View Log Semantics
Oracle 9iR2 Data Warehousing Guide – Types of Materialized Views – on which types of MVs (# Materialized Views with Aggregates, Materialized Views Containing Only Joins, Nested Materialized Views) we can discern and what requirements for each of these are. For example:
The SELECT clause in the materialized view creation statement defines the data that the materialized view is to contain. Only a few restrictions limit what can be specified. Any number of tables can be joined together. However, they cannot be remote tables if you wish to take advantage of query rewrite. Besides tables, other elements such as views, inline views (subqueries in the FROM clause of a SELECT statement), subqueries, and materialized views can all be joined or referenced in the SELECT clause. For fast refresh to be possible, the SELECT list must contain all of the GROUP BY columns (if present), and there must be a COUNT(*) and a COUNT(column) on any aggregated columns. Also, materialized view logs must be present on all tables referenced in the query that defines the materialized view. The valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX, and the expression to be aggregated can be any SQL value expression.
On Join-views:
Oracle does not allow self-joins in materialized join views. If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are:
* A materialized view log must be present for each detail table.
* The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.
* If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause. However, if there are outer joins, the WHERE clause cannot have any selections. Further, if there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
* If there are outer joins, unique constraints must exist on the join columns of the inner table. For example, if you are joining the fact table and a dimension table and the join is an outer join with the fact table being the outer table, there must exist unique constraints on the join columns of the dimension table.If some of these restrictions are not met, you can create the materialized view as REFRESH FORCE to take advantage of fast refresh when it is possible. If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be fast refreshable with respect to the other tables for which all the criteria are met.
A materialized view log should contain the rowid of the master table. It is not necessary to add other columns.
To speed up refresh, you should create indexes on the materialized view’s columns that store the rowids of the fact table.
Related posts:
How to drive your colleagues nuts – dbms_advanced_rewrite (Oracle 10g) Cost Based Optimizing through time travel? – the value of meta-data for enabling Query Rewrite Oracle Data Mining – from SQL and PL/SQL Testing Knowledge of Database Performance – Is this a good Execution Plan? 
OOW 2007: (most) presentations available for download
[…] Building applications in our corner of the world typically revolves around databases. The most important bit about an application is not the application, but rather the data it creates and processes that gets stored in the database. And even before security and performance, the integrity of that data is our prime concern. Making absolutely sure our database is robust is essential. Enforcing all data oriented business rules is a sine qua non for a successful application.In this article I will discuss a typical way of implementing business rules in the Oracle database, using PL/SQL and table triggers. An approach taken by thousands of organisations and ten of thousands or more developers. An approach proven over the years, one would think. An approach used in a business rule frameworks like CDM RuleFrame. And an approach that is fundamentally flawed. Date centric business rules come in a number of varities. Some pertain to attributes – like NAME is a String of up to 25 characters or SALARY may not exceed 10,000 or JOB can be one of the following values. Others apply to tuples -entire records – like: an employee with JOB unequal to SALESMAN may not have a (value for) COMMISSION, a CLERK may not earn more than 3000 or You cannot Manage yourself (EMPNO should not be equal to MGR). These rules can be implemented using declarative constraints in the database – CHECK CONSTRAINTS. Other declarative constraints are Primary Kyy and Unique Constraints and Foreign Key or Referential Constraints. The former allow us to implement rules like no two employees may have the same name and job (that would be utterly confusing) or there can be only one employee in each job per department. The latter enforces rules such as every Employee must belong to a Department. Implementing a business rule using a declarative constraint is perfect. These constraints are built into the database kernel, are optimized for performance and are guaranteed to be enforced. When these constraints are enabled, we have the certaintity that the data in our tables complies with the rules. That is as robust as it can get.However, more complex data rules cannot be enforced using these declarative constraints. Let’s take a look at a simple example. Again in the EMP and DEPT tables in the SCOTT schema – you may have seen them before – we have the following rule: "no more than three clerks in a department" . Other options: Unique Expression Based Index and Check Constraints on Materialized ViewsUsing Unique Index on Expressions – see for example Using unique function based index as check constraint – we can, still declaratively, enforce rules like: no more than one CLERK per Department or not more than one birthdays per department per month. But even then at some point declarative solutions fall short, and we have to turn to PL/SQL for implementing advanced complex rules.Another option is available through Materialized Views. Though perhaps not best known for the role they can play in implementing and enforcing business logic, Materialized Views nevertheless are a powerful instrument for rules that are too complex for normal declarative constraints or Exression Based Unique Indexs. The MV route is clean: no triggers or supporting packages are needed. Note that the Materialized View option – see also Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints) – powerful as it may be is still limited to a subset of complex business rule we will have to implement in our databases.Applying the Materialized View approach to our example of ‘no more than three Clerks per Department’ we could implement that rule as follows (for explanation and details see the article referred to above):Create a Materialized View Log for Table EMP: since we want the rule to be enforced at commit time, we need a REFRESH ON COMMIT Materialized View and for that we need to have Materialized View Log; note that this View Log can be reused for all complex business rules concerning table EMP.Create a Materialized View on table EMP for this particular Business Rule; we may be able to reuse the MV for other business rules as wellCreate a Check Constraint on the Materialized View that enforced the business ruleThe steps are pretty simple, especially compared to what we have seen before when implementing the same rule using PL/SQL:1. Create the Materialized View Log for table EMPcreate materialized view log on emp with rowid(deptno, job) including new values; 2. Create the Materialized View for the Business Rulecreate materialized view emp_br001_mvrefresh fast on commitasselect deptno, count(*) cntfrom empwhere job = ‘CLERK’ group by deptno/ 3. Add the Check Constraint to enforce the rulealter table emp_br001_mvadd constraint check_cntcheck ( cnt < 4)deferrable; Note: since the MV will only be refresh when the transaction commits, the check constraint will take effect only at that time. It is somewhat like a deferred (declarative) constraint. In the actual time-line, this constraint will be enforced after all ‘normal’, table based deferred declarative constraint were successfully enforced. That is a good thing as you would like to enforce this business rule at the end of the transaction, rather than after each individual statement. Transferring Clerks between department would otherwise become hopelessly complex.Putting this MV based implementation to the testThe starting point is the following:SQL> select ename 2 , deptno 3 from emp 4 where job = ‘CLERK 5 order 6 by deptno 7 /ENAME DEPTNO———- ———-MILLER 10SMITH 20ADAMS 20JAMES 30 Now we open two database sessions and make some changes in the data: […]
[…] Back in 1998 or 1999 I filed a bug/enhancement request in the Oracle bug database. As I was still working at Oracle back then and I had direct access to the bug database – primarily for the many beta testing activities I was involved in. The request was for an On Commit trigger, to fire immediately before the commit process would irrevokably be under way. The trigger should allow me to do some last minute business rule enforcements, to make sure the transaction was really, really okay.At the time I was working on Oracle’s CDM RuleFrame framework for the implementation of data oriented business rules. It was somewhat novel at that time, as it focused on transaction level business rule enforcement. Our philosophy was: as long as you do not commit, it does not matter one bit if the data is not in line with all data integrity rules. Who cares? At time you commit – that is when it should be okay. The kind of same idea behind the deferred declarative database integrity constraints. The problem we were facing is that we had no way to invoke this transaction level validation of rules when the application commits. The database will take care of ’simple’ declarative constraints, and for multi-table constraints or complex single table constraints that had to be programmed in PL/SQL, we could do nothing but enforce applications to perform this validation by deliberately invalidating a deferred check constraint from before statement level triggers for each DML type on all tables protected by our special business rules. Only if our CDM RuleFrame validation code had been called would we rectify that constraint violation and allow the transaction to continue. What I wanted to have was a way for the database to automatically invoke the CDM RuleFrame validation logic when the transaction is committed.The enhancement request never saw any action. So today, seven years onwards, we still do not have an On Commit trigger in the database. There some ways to emulate one, but none of them is ideal. In this article a brief report on what hooks we have for our make-believe On Commit trigger. The proceedings in the database surrounding the commit of a transaction include the following steps:validate deferred constraintsdelete all data from Temporary Tables with ON COMMIT DELETE ROWSsynchronize Materialized View with ON COMMIT REFRESHoperations on Materialized View Logs? Operations on Changed Data Capture Window?And once the transaction is really found to be okay:synchronize redo log buffers to filerelease locks ’submit’ pending jobssend alertsI have looked into these events to see which one could be used as a trigger for my own additional PL/SQL code.Deferred Constraints I had some hopes for Validation of Deferred Constraints. Well, that takes place but does not fire any triggers. I once thought that a Deferred Foreign Key Constraint with ON DELETE CASCADE in combination with a delete trigger on the referring (Child) table would work: the parent is deleted somewhere during the transaction, the ON DELETE CASCADE is part of a deferred constraint and therefore takes place after the application has sent the commit command. Well, no: it turns out that even for deferred constraints, the cascade deletion of children is Immediate.Then I hoped that perhaps in 10gR2 the DML Error Log feature – which allows us to complete statements that violate declarative integrity constraints (see for example: http://orafaq.com/node/76) while writing the offending records to a logging table – would help out: an insert trigger on this logging table in combination with a deferred constraint that always gets violated during my transactions surely would do the trick? No, it does not, as DML Error Log does not work for deferred constraints…Note that when validation of deferred constraints runs into a violation, the entire transaction gets rolled back. There is an absolute minimum in terms of information on what violation for which record caused this rollback.Temporary TablesOne other event at commit time is the emptying of temporary tables with ON COMMIT DELETE ROWS set. That means that when the transaction commits, all records are flushed out of the TEMPORARY table. Well, I understood it would be naive to simply create a DELETE trigger on the temporary table (which I can do) and expect it to be fired if the records are removed at commit time (which it does not). The flushing of records from the temporary table is a low level, no log action – much like TRUNCATE. I still have to check whether perhaps a TRUNCATE system even level trigger would do the trick (though I doubt it).I though a smart approach would be: foreign key to temporary table with ON DELETE CASCADE and a delete trigger on the child table. However, it turned out that Referential Constraints to temporary tables are not allowed in the database. Another idea bites the dust. Refreshing Materialized ViewsWell, that means I am back to a solution I had found quite some time ago: the Materialized View. See for example the article Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints) for some background. And more….?I still want to investigate if the changed data capture somehow gives me any hooks for event triggers. I should also investigate the TRUNCATE trigger on tempoary tables – though I doubt this approach: I am not sure if this event may be only after the commit itself, too late for my purpose. I am also not exactly clear on what Materialized View Logs exactly go through.If you would have any suggestions for mechanisms in the database that may help me get as close to a commit trigger as I can get, I would be thankful. […]
[…] With the advent of the Oracle 7 RDBMS – 1993 – a new era dawned. Many of the key database functions and facilities, such as (Enforced) Integrity Constraints, Stored PL/SQL, Triggers, Snapshots, supplied packages and key SQL features were introduced in the Oracle 7 releases. Many Oracle developers also seem to be anchored in terms of knowledge and experience in the Oracle 7 database and the features and functions that release 7 had to offer. Even though we have moved on. And Oracle has moved on! It turns out that many very experienced developers are still developing in their Oracle 9i and 10g environments as though they live in the world of Oracle7. Every now and again they may accidentally make use of a more recent feature and of course they have read and heard about this and that new option, but Oracle7 is what’s at their fingertips.By the, not too long ago, I found myself in that exact same position. Programming SQL and PL/SQL in a very backwards compatible way! Realizing that predicament, I embarked on a program to study and take in the Oracle 8i, 9i and more recently 10g stuff that somehow had escaped my notice. This then turned into a workshop for my colleagues at AMIS, initially three days, now four very full days. This Oracle 7Up Workshop allows experienced SQL and PL/SQL Developers to get acquainted with (most of) everything that was added in Oracle 8i, 9i and 10g that is of practical use. It is not a marketing-style new features overview, it is an serious discussion of important, useful, timesaving, performance enhancing functionality. We quickly realized how this training would not only benefit the staff at AMIS: we also offered it to our business partners. By now we have trained a substantial number of software developers, including Oracle DBAs, Oracle Application Developers and Java developers who wanted to better grasp the full potential of SQL in an Oracle database. Most of if not all attendant were able to apply knowledge and code samples from the training in their own jobs starting the day after the training. Next week – on Thursday 20th april and Friday 21st April addressing SQL- and the week after – Thursday 28th and Friday 29th of April addressing PL/SQL- we have another edition of the Oracle 7Up Workshop (at our office in Nieuwegein, The Netherlands). And there are a few seats available for external participants. You can participate in one of the two blocks or attend both. Details on registration can be found here: AMIS Trainingen. Topics in the 7Up Training Now what are all these grand new features that we should know about, I hear you ask. Well, some of the major ones in the SQL session are: Analytical Functions, Multi-Table Insert and Merge, In Line Views, FlashBack Queries, Materialized Views, Oracle Text, Advanced Constraints implementation, Database Design (Index Organized Tables, Temporary Tables, Function Based Indexes, Instead Of Triggers), Join Syntax, Hierarchical Queries.The PL/SQL session discusses topics like: Collections and Object Types, bulk operations, dynamic SQL, fine grained access control and auditing, autonomous transactions, system event triggers, new PL/SQL functions and syntax. For more details and examples of the slides and the labs used in this workshop, see Oracle 7Up Training – Sample of Materials.Discussion of "7Up Topics" on the weblogMany of topics that we discuss in the 7Up training have been subject of one or more articles on our weblog. Recent examples of such articles include:Default Column Value – the syntax for defining default values in your table definitions is richer than you probably realize, including the option to dynamically set default values, depending on the context How using ROWNUM in In-Line Views can speed up your query – ROWNUM seems like a pretty innocent, not very exciting ‘feature’ of SQL Queries. However, including ROWNUM in a query can have pretty drastic effects – and sometimes positve ones – on your query performanceFunctions NVL2 and NULLIF – some new functions that can make constraint definitions and where clauses more compactFinding overlap between time-periods using LAG and LEAD – Analytical functions allow you to look forward or backward in a set of rows, reducing the need for many complex joins!Materialized view as mechanism for the declarative (PL/SQL free) implementation of complex business rules – Declarative implementation of business rules like Master must have a restricted number of details (no fewer than X and not more than Y), The Sum of a certain Detail Column is restricted within a master and The start-date and end-date of detail-records must be between the Master’s Startdate and Enddate: it can be done!How we can edit files through simple insert and update operations – Peter Kok’s article on Updateable External TablesRapidly building a WebSite search engine using Oracle Text – Investigating Bill of Materials – Juggling with tree-structures and hierarchical queries using Oracle 9i and 10g Connnect By enhancementsAspect Oriented Programming in PL/SQL – Making surprising use of the After Create (on compile) TriggerMerge! – a real li(f/v)e saver and many more…. […]
For more explanation around this use of Materialized Views, seehttp://www.dbazine.com/oracle/or-articles/tropashko8 for an interesting discussion about the same topic. It discusses rules such as Unique Key Constraint Spanning Multiple Tables and Cardinality Constraints.
Hi Shafi,
Do you have SUM and COUNT columns in your TEMPTRAN table? Or is this a typo and did you want something like:
(I assume that BALANCE, CNT, CNTAMT are columns in your TEMPTRAN table...)
While creating materialized view on single table with the following conditions?
I am getting error, any solutions?
SQL>CREATE MATERIALIZED VIEW LOG ON TEMPTRAN WITH
ROWID, (LEDGERCD, SUBLEDCD, BRCD, VALUE_dATE, VCHCODE)
INCLUDING NEW VALUES;
sql>CREATE MATERIALIZED VIEW MV_TEMPTRAN
REFRESH FAST ON COMMIT
AS
SELECT LEDGERCD, SUBLEDCD, BRCD, VALUE_DATE, VCHCODE,
SUM(VCH_AMT_MV) BALANCE, COUNT(*) CNT,
COUNT(VCH_AMT_MV) CNTAMT
FROM TEMPTRAN
WHERE authorised = ‘Y’
GROUP BY LEDGERCD, SUBLEDCD, BRCD, VALUE_DATE, VCHCODE;
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Ik onderscheid twee soorten beperkingsregels, die ieder op hun beurt weer verder onderverdeeld kunnen worden. Allereerst de regels die een niet-toegestane toestand (NTT) beschrijven en als tweede de regels die een niet-toegestane overgang (NTO) beschrijven.
En niet-toegestane overgang is de combinatie van één toegestane begintoestand en één toegestane eindtoestand. Een toestand is een stelsel van feiten die op één moment in de tijd waar zijn.
Een voorbeeld van een NTT:
1. Persoon met sofi-nummer 1560.91.642 is geboren op 07-07-1970
2. Persoon met sofi-nummer 1560.91.642 is geboren op 31-07-1968
Deze twee zinnen kunnen niet tegelijkertijd waar zijn. Een persoon kan niet op twee verschillende tijdstippen geboren zijn. Daaruit kan men afleiden dat het sofi-nummer een unieke identificatie is voor een persoon. Dat resulteert in een primaire of alternatieve sleutel.
Een voorbeeld van een NTO is :
Begintoestand (TT1)
1. Persoon met sofinr 1560.91.642 ontvangt een AOW uitkering
Eindtoestand (TT2)
2. Persoon met sofinr 1560.91.642 ontvangt een WW uitkering
Een persoon mag geen WW uitkering ontvangen nadat hij/zij een AOW heeft ontvangen.
De NTT kunnen in een relationele database worden gecontroleerd met SQL. In bovengenoemd geval betekent dat de vraag “Welke personen zijn geboren op meer dan één tijdstip� niet beantwoord mag worden, c.q. een lege verzameling moet zijn. Indien dat wel het geval is, krijgt de desbetreffende gebruiker in het gunstigste geval de melding welke (combinatie van) zinnen welke beperkingsregel overtreden.
De meeste databases hebben de mogelijkheid bepaalde type beperkingsregels declaratief vast te leggen, dus zonder gebruik te maken van SQL.
De NTO kunnen alleen met SQL worden gecontroleerd indien er historische informatie opgeslagen is, zodat men de feiten van de begintoestand kan reproduceren. In bovengenoemd geval betekent dat de feiten als volgt beschreven moeten worden:
1. Persoon met sofinr 1560.91.642 ontvangt als eerste een AOW uitkering
2. Persoon met sofinr 1560.91.642 ontvangt als tweede een WW uitkering
en men dus een NTT beschrijft.
Indien dit niet gewenst is, kan men dit met behulp van database triggers controleren.
De diverse feiten worden achtereenvolgens bekend gemaakt aan de database. In het geval dat de zinnen in diverse administratiestructuren (tabellen) vastgelegd worden, kan de controle alleen achteraf, dus nadat alle relevante feiten bekend zijn gemaakt. Dat betekent dat er meerdere statements nodig zijn. Ik miste altijd de mogelijkheid zaken te controleren op transactie-niveau, zeg maar een PRE-COMMIT trigger. Voor de afleidingsregels (of rekenregels) mis ik nog een POST-COMMIT trigger.
Door op deze creatieve wijze gebruik te maken van de mogelijkheden van de Oracle database, betekent dit dat elke beperkingsregel op deze wijze gecontroleerd kan worden. En zodoende de (niet officiële) PRE-COMMIT trigger is geboren.
Ik mis inderdaad nog de mogelijkheid om de gebruiker mede te delen welke regels overtreden zijn en welke combinaties van zinnen hieraan ten grondslag liggen.
De mooiste user interfaces sluiten overtredingen uit. De op één na mooiste user interfaces geven de gebruikers de mogelijkheid de algemene regel te kennen en correctief op te treden, dus de zinnen aan te passen die de regel overtreden.