Skip to Main Content
  • Questions
  • Your opinion about the 'Rulegen 3.0' tool

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sinaî.

Asked: August 30, 2011 - 10:11 am UTC

Answered by: Tom Kyte - Last updated: September 06, 2011 - 8:51 am UTC

Category: Database - Version: 11gR2

Viewed 1000+ times

You Asked

Hye Tom,

Can you give us your opinion about the 'Rulegen 3.0' tool. http://www.rulegen.com/

Is Oracle Working on giving these functionalities in the database, more precisely the possibility of Implementation of 'complex' data constraint business rules ?

and what about other impacts : database performance vs application performance, RAC, use of virtual column, ...

Thanks.

and we said...

I can say this - if you are going to write triggers or application logic to enforce some complex logic, I would frankly prefer that those triggers/logic be generated by someone that has given it a *great* deal of thought and time.

And - this tool was written by someone that has done that great deal of thinking.

The problem I have with triggers is that many (i would say most, the vast majority) people that write them do not understand how they will work in a multi-user environment.

They do not think about the race conditions.

They do not think about reads not being blocked by writes.

They don't think about the implications of using an autonomous transaction to work around the "mutating table" constraint.

Pretty much most every hand developed trigger/application logic I see to enforce complex integrity constraints (constraints that cross rows in a table - like "the average salary by department cannot exceed 5000" - or constraints that cross tables - like referential integrity) are just done plain wrong. Their implementation is flawed and no one knows it until it is too late.

The same goes true for applications that try to enforce this type of integrity constraint.

The author of this particular tool understands these issues and has programmed the generated code in such a way to deal with them correctly - barring bugs of course (I'm not saying it is perfect, it could well be - just that I would trust it before most other hand generated solutions)

We are adding to the database all of the time - there may come a day when something like this would become moot - but I don't see that happening right now, nor in the near future. I cannot see any further than that myself ;)

and you rated our response

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

my 2 cents

September 01, 2011 - 7:22 am UTC

Reviewer: Sokrates

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

September 02, 2011 - 12:44 am UTC

Reviewer: Marco Gralike from The Netherlands

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.
Tom Kyte

Followup  

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

September 02, 2011 - 7:45 am UTC

Reviewer: Tharg

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

September 02, 2011 - 10:16 am UTC

Reviewer: Duke Ganote from 39.06,-84.24

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?

September 03, 2011 - 3:12 am UTC

Reviewer: Stew Ashton from Paris, France


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?
Tom Kyte

Followup  

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

September 03, 2011 - 4:03 pm UTC

Reviewer: Stew Ashton from Paris, France


Thanks much, Tom. That makes perfect sense.

@Stew

September 03, 2011 - 4:17 pm UTC

Reviewer: Sokrates

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

September 04, 2011 - 9:25 am UTC

Reviewer: Toon Koppelaars from Netherlands

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

September 04, 2011 - 3:15 pm UTC

Reviewer: Stew Ashton from Paris, France


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'

September 05, 2011 - 9:59 am UTC

Reviewer: Sokrates

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

September 05, 2011 - 11:30 am UTC

Reviewer: John Stegeman from UK

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

:)
Tom Kyte

Followup  

September 06, 2011 - 8:51 am UTC

well said.

for who is interested in the question I posed

September 05, 2011 - 2:49 pm UTC

Reviewer: Sokrates

found the answer on
http://technology.amis.nl/blog/2981/rulegen-the-next-generation-framework-for-data-logic-aka-data-oriented-business-rules
:
dbms_lock ( of course, what else )

and it seems, that they use locks quite economically - lock only what is really necessary in order to implement a rule.

To the original poster of this thread:
Thanks for pointing out rulegen - it looks quite interesting !

@Sokrates

September 06, 2011 - 8:18 am UTC

Reviewer: Toon Koppelaars from Netherlands

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

September 06, 2011 - 8:27 am UTC

Reviewer: Toon Koppelaars from Netherlands

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

September 06, 2011 - 9:38 am UTC

Reviewer: Stew Ashton from Paris, France


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.

September 06, 2011 - 11:59 am UTC

Reviewer: Toon Koppelaars

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

September 09, 2011 - 5:09 pm UTC

Reviewer: Narendra from UK

Here is one issue that Rob has discussed in the past
http://rwijk.blogspot.com/2010/01/enq-ji-contention.html

Harmful, harmful triggers

November 22, 2011 - 7:57 am UTC

Reviewer: Toon Koppelaars from Netherlands

> 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