my 2 cents
Sokrates, September 01, 2011 - 7:22 am UTC
a. the framework looks a bit like a substitute for Oracle's lack of implementing ASSERTIONs (specified by SQL92)
( compare
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:698031000346429496 )
b. implementing ASSERTIONS via after-statement-triggers or on-commit-materialized-views may work for small databases, but cannot be used in OLTP as soon as tables have reached some size limit ( I think this is the main reason why ASSERTIONs are not implemented yet )
Balance
Marco Gralike, September 02, 2011 - 12:44 am UTC
There is always a balance in things especially regarding performance. In nowadays service orientated architectures the skill set of people setting it up regarding Oracle knowledge is most of the time not adequate enough. As someone said it once to me, "I need al my time for being a good programmer doing also the Oracle, database, thing is just too much...". That might be the cause that I see more and more performance problems arising due to ineffective use of the database by eg. the middle tier environments. If this can be avoided by shielding the database via Rulegen of this inadequate use then both sides, database & middle tier, should be glad implementing a solid architecture. The overall is probably better then using the database as a bit bucket with standalone tables holding only data.
September 02, 2011 - 8:37 am UTC
that aligns with my opinion as well - I'd rather have a tool that was written by someone that knows what to do than rely on many application developers who do not understand transaction processing, how the database works concurrency controlwise, and multi-user race conditions.
@Sokrates
Tharg, September 02, 2011 - 7:45 am UTC
I believe the reason that Oracle doesn't implement assertions is purely financial.
I'm involved in a project where database integrity constraints are not being used and the application is doing it instead. Despite my best efforts, this is set to continue. The reason is that businesses just don't see the importance or significance of declarative constraints versus triggers or application validation.
Since businesses aren't asking for it, there's no commercial advantage in Oracle corporation providing something which we all recognise as tremendously useful, but which won't make money. The fact that the only sure-fire way to ensure integrity is via a stored procedure interface says it all. Nobody writes applications with only stored procedure interfaces, and oracle don't provide the correct alternative declarative features in the database. Even if Oracle did, the facility wouldn't be used.
Folks don't understand, don't care, and hence don't ask for the feature, so Oracle don't provide it.
Money talks.
regarding BALANCE, etc
Duke Ganote, September 02, 2011 - 10:16 am UTC
Regarding: "I need all my time for being a good programmer doing also the Oracle, database, thing is just too much...".
I fully understand that! I'll probably never bother with client-side programming; it's way too faddish
http://oraclesponge.wordpress.com/2011/04/13/independence/ Database technology certainly advances and has its challenges too. Yet the "balance" is wrongly skewed toward the client-side.
I just had a case this week where a programmer was reading from the database, then taking half-an-hour for some "sophisticated" local processing. It took a while to read through the spec, and fully understand it... but I reduced it to a few lines of SQL (encapsulated in a view) that returns in sub-second time.
Size limit for ASSERTIONS?
Stew Ashton, September 03, 2011 - 3:12 am UTC
Sokrates said:
"implementing ASSERTIONS via after-statement-triggers or on-commit-materialized-views may work for small databases, but cannot be used in OLTP as soon as tables have reached some size limit."
Sokrates, why do you say that? What problems have you faced, especially with MV-based assertions?
Tom, do you know of any general caveats about scalability of these solutions?
September 03, 2011 - 11:22 am UTC
it would really depend on the complexity of the materialized view - but an on commit fast refresh one would be typically pretty scalable from a data perspective - but do remember that it will still encounter some serialization (you are typically aggregating many rows into one) - and that could affect your scalability based on concurrent users, not size of data so much
Thanks for reply on MV scalability
Stew Ashton, September 03, 2011 - 4:03 pm UTC
Thanks much, Tom. That makes perfect sense.
@Stew
Sokrates, September 03, 2011 - 4:17 pm UTC
that was mostly speculation, i have no proof.
sorry, i should have written that.
i never implemented on-commit-materialized-views myself in production, mainly because i don't trust too much in the stability of their implementation.
when i was playing around with them - in 10.2 - i encountered too many internal errors, as far as i can remember. There might be a good chance that this has improved in 11g, though - i don't know.
Tom, thank you for pointing out your opinions on this.
Assertions, MV's, triggers...
Toon Koppelaars, September 04, 2011 - 9:25 am UTC
One of the issues with using materialized views (combined with the typical CHECK( 0 = 1 ) constraint defined against them) as a workaround for implementing assertions is, that they cannot be used for all assertions. As Tom stated, only when the (complexity of the) assertion is such, that the required 'workaround' MV would be fast-refreshable, will you get an implementation that might scale good enough for you. This, in practice, represents a small portion of the assertions that I encounter inside customers' database designs.
Lifting all restrictions that Oracle currently has in order for an MV to be possibly fast refreshable, is scientifically the exact same problem of providing an efficient implementation of the ANSI SQL assertion concept. Once assertions are available in a scalable manner, all MV's will be (as) fast refreshable (as logically possible). And vice-versa, once every conceivable MV is (as) fast refreshable (as logically possible), will implementing assertions be a piece-of-cake.
And the reason why Oracle, nor any other vendor, cannot provide us with this, is simple: scientific research hasn't closed this topic yet. It hasn't been figured out how to, given an arbitrarily complex assertion (= SQL expression with subqueries that evaluates to either true or false), and given an arbitrarily complex transaction, how to compute the most efficient way to ensure the continued validity of that assertion, given that transaction.
In the meantime we can develop a framework where you input the end result of your thinking about 'how to compute the most efficient way to ensure, etc', and which outputs trigger-code that implements the assertion.
Which is what I did.
The author :-)
@Toon: why always triggers, why never MVs
Stew Ashton, September 04, 2011 - 3:15 pm UTC
Toon,
Thanks for contributing, your voice carries weight.
You argue that fast refresh on commit MVs cannot do *everything*, but they can do *some* things. So why do you always generate trigger code and never MVs? This is a serious question, not rhetorical.
What I find interesting in MV-based constraints is that Oracle can optimize the underlying SQL for either few or many updates within the same transaction.
The example I see in the RuleGen website could be done with MVs. Does the site give an example of a rule that only triggers can handle?
I doubt that after statement triggers can be used at all to 'validate an assertion'
Sokrates, September 05, 2011 - 9:59 am UTC
So, rulegen does only create triggers, and does not use MV's at all ?
However, I cannot see how this could work, I think, there is a multi-user-committed-read-issue here:
(Tom, you wrote:
"The author of this particular tool understands these issues and has programmed the generated code in such a way to deal with them correctly", could you shed some more light onto this ?)
Let's take a look at the example
“At most one president allowed in EMP”
given in
http://www.rulegen.com/sites/default/files/rulegen_30.pdf In order to 'validate' this rule,
Select 'At most one president
allowed. Found'||
to_char(num_pres)||'.' as MSG
from (select count(*) as num_pres
from EMP
where job='PRESID'
)
where num_pres > 1
will be used in an after-statement-trigger, right ?
So:
create table emp(empid int primary key, job varchar2(20));
create trigger tr_val_emp
after insert or update on emp
declare vmsg varchar2(100);
begin
Select 'At most one president
allowed. Found'||
to_char(num_pres)||'.' as MSG
into vmsg
from (
select count(*) as num_pres
from EMP
where job='PRESID'
)
where num_pres > 1 ;
raise_application_error(-20999, vmsg);
exception when no_data_found then null;
end tr_val_emp;
/
Is that the idea ?
If so, that will work perfectly in a single-user-environment, but will break easily when going multi-user:
single-user > delete emp;
2 rows deleted.
single-user > commit;
Commit complete.
single-user > insert into emp values(1, 'WORKER');
1 row created.
single-user > insert into emp values(2, 'PRESID');
1 row created.
single-user > insert into emp values(3, 'PRESID');
insert into emp values(3, 'PRESID')
*
ERROR at line 1:
ORA-20999: At most one president
allowed. Found2.
ORA-06512: at "SOKRATES.TR_VAL_EMP", line 12
ORA-04088: error during execution of trigger 'SOKRATES.TR_VAL_EMP'
single-user > select * from emp;
EMPID JOB
---------- ------------------------------------------------------------
1 WORKER
2 PRESID
good !
now, let's going multi-user:
multi-user > delete emp;
2 rows deleted.
multi-user > commit;
Commit complete.
multi-user > insert into emp values(1, 'WORKER');
1 row created.
multi-user > insert into emp values(2, 'PRESID');
1 row created.
multi-user > declare pragma autonomous_transaction;
2 begin
3 insert into emp values(3, 'PRESID'); -- TR_VAL_EMP fires but does not see the PRESID with empno 2 not yet committed in another transaction
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.
multi-user > commit;
Commit complete.
multi-user > select * from emp;
EMPID JOB
---------- ------------------------------------------------------------
1 WORKER
2 PRESID
3 PRESID
Bummer !
So please, could someone explain to me, how to use triggers to validate “At most one president allowed in EMP” at all ?
I think, the only possible way to do this is an on-commit-MV
Declarative vs application validation
John Stegeman, September 05, 2011 - 11:30 am UTC
"The reason is
that businesses just don't see the importance or significance of declarative constraints versus
triggers or application validation."
Well, businesses shouldn't care how we do it, but they *should* care about the end result. If we as technology people can't make the right decisions for ourselves (and be able to articulate the business benefits such as reusability, reliability of the validation, etc) then, what exactly *are* we doing?
:)
September 06, 2011 - 8:51 am UTC
well said.
for who is interested in the question I posed
Sokrates, September 05, 2011 - 2:49 pm UTC
@Sokrates
Toon Koppelaars, September 06, 2011 - 8:18 am UTC
Yes indeed, we use DBMS_LOCK to serialize whenever necessary.
Here's your example using the generated triggers:
(I prefer to start a new sqlplus session to do the things 'concurrently'. The 'rule_scope' call just ensures all other rules are temporarily disabled, so they don't interfere with this test)
<code>
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 6 05:32:03 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: am4dp30/xxx
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from emp where job='PRESIDENT';
More...
COUNT(*)
----------
0
1 row selected.
SQL> desc emp
Name Null? Type
------------------------------------------------------- -------- ----------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME NOT NULL VARCHAR2(8)
JOB NOT NULL VARCHAR2(9)
BORN NOT NULL DATE
HIRED NOT NULL DATE
SGRADE NOT NULL NUMBER(2)
MSAL NOT NULL NUMBER(7,2)
USERNAME NOT NULL VARCHAR2(15)
DEPTNO NOT NULL NUMBER(2)
SQL> exec sysrgrt30.sp_rgr_set_rule_scope('E_TAB02');
PL/SQL procedure successfully completed.
SQL> insert into emp values(4000,'Toon','PRESIDENT',trunc(sysdate)-(46*365),trunc(sysdate),5,12000,'TKOPPELA',15);
1 row created.
SQL> host
[oracle@localhost work]$ sqlplus am4dp30/xxx
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 6 05:32:03 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into emp values(4001,'Tom','PRESIDENT',trunc(sysdate)-(46*365),trunc(sysdate),5,12000,'TKYTE',15);
insert into emp values(4001,'Tom','PRESIDENT',trunc(sysdate)-(46*365),trunc(sysdate),5,12000,'TKYTE',15)
*
ERROR at line 1:
ORA-20998: Unable to acquire business-rule lock (RG$E_TAB02/X (r1)). Blocked by SID 178.
ORA-06512: at "AM4DP30.X_RGG30_AD_E_TAB02", line 218
ORA-06512: at "AM4DP30.RGG30_E_AIUDS", line 20
ORA-04088: error during execution of trigger 'AM4DP30.RGG30_E_AIUDS'
SQL>
</code>
@Stew
Toon Koppelaars, September 06, 2011 - 8:27 am UTC
Stew,
I've had numerous ORA-00600 encounters with MV's (like others have had). But those will all be solved in the end, or might all have been already. I guess my main concern with using the MV-trick is that they always cause some serialization. Even when they need not.
Here's an example:
create table emp
(empno number not null primary key
,ename varchar2(20) not null
,job varchar2(10) not null)
/
create materialized view log on EMP with rowid, primary key(job) including new values;
create materialized view NOT_TOO_MANY_PRESIDENTS
build immediate refresh fast on commit as
select count(*) num_of_presidents
from EMP
where job = 'PRESIDENT';
alter materialized view NOT_TOO_MANY_PRESIDENTS add constraint NOT_TOO_MANY_PRESIDENTS
check (num_of_presidents <= 1) deferrable;
Let's put some data in:
insert into emp values(1,'Toon','NO-PRES');
commit;
Let's put some more data in, and trace an insert of another non-president, for instance:
insert into emp values(2,'Tom','NO-PRES');
you'll see in the trace the following MV-refresh happening:
UPDATE (SELECT "SNA$"."NUM_OF_PRESIDENTS" "C0", "AV$"."D0"
FROM "WORK"."NOT_TOO_MANY_PRESIDENTS" "SNA$"
, (SELECT SUM(1) "D0"
FROM (SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."JOB"
FROM "WORK"."MLOG$_EMP" "MAS$"
WHERE "MAS$".XID$$ = :1 ) "DLT$0"
WHERE ("DLT$0"."JOB"='PRESIDENT')
GROUP BY 1)"AV$") UV$
SET "C0"="C0"+"D0"
/
Studying this UPDATE, you'll see that Oracle is summing the number of new presidents so that it can increase the value in the MV. Which it (updating the MV) always does. But there is no need to update the MV in this case, there is only the need to detect that there is no such need ;-). And this (the latter) need, can be done concurrently, since it only involved your rows in the MV-log, which no one else can see.
Since it is always trying to refresh the MV, you'll always hit serialization. Since this transaction is inserting a non-president, no serialization should happen at all.
The triggers generated by RuleGen will detect that no serialization is needed for this case. But again, this is due to 'your thinking' and telling that 'hey only serialize for this assertion, whenever a president is inserted, or some non-pres. got promoted to a president'.
@Toon: thanks
Stew Ashton, September 06, 2011 - 9:38 am UTC
Toon, thank you for this answer. I will look into my own MVs to see if I am causing unnecessary serialization.
Just for other readers, I would never use an MV for this rule, I would use a function-based index:
CREATE UNIQUE INDEX only_one_president ON emp(CASE JOB WHEN 'PRESIDENT' THEN JOB END);
Many ways to implement assertions.
Toon Koppelaars, September 06, 2011 - 11:59 am UTC
You've just given me another reason.
I like a single approach.
I dislike mixed approaches.
- Some rules declarative.
- Some rules with MV-trick.
- Some rules with function based index trick.
- Some rules with triggers.
- Some rules in the "busines logic layer."
- Some rules with ...feature xyz coming up in Oracle12...
My choice is: do declarative what can be done declaratively. Use triggers for the rest.
And as soon as we get assertions, will triggers become a (almost) obsolete feature.
I might start a new blog where I document the "'Triggers considered harmful', considered harmful" presentation I delivered at Hotsos earlier this year...
Assertion using MV - Issue
Narendra, September 09, 2011 - 5:09 pm UTC
Harmful, harmful triggers
Toon Koppelaars, November 22, 2011 - 7:57 am UTC
> I might start a new blog where I document the "'Triggers considered harmful', considered harmful" presentation I delivered at Hotsos earlier this year...
I finally got around to starting that:
http://harmfultriggers.blogspot.com