Re: materialized view to enforce business rule

  • From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
  • To: "Chris.Stephens" <Chris.Stephens@xxxxxxx>
  • Date: Wed, 21 Jul 2010 21:05:13 +0200

>
> A developer emailed me indicating he was able to violate this constraint
> without error.
>

Did the developer say how that was done?

Does anyone see a problem with relying on this method to enforce the rule?
> Theoretically at least.
>

Not in theory, no. You do need to be aware that the Mview efficiently
enforces the rule. This has two dimensions:
1) you would want the refresh to be fast. In this case only refresh the row
of the project that got changed in the transaction, and not all other
projects. When business rules become complex, Mviews might not be efficient
enough, due to the fact that they're not 'fast refreshable' anymore.
2) there is always serialization involved when (multi-row) business rules
require validation. Rob van Wijk wrote a blogpost on this, when you use
Mviews to do so:

http://rwijk.blogspot.com/2010/01/enq-ji-contention.html



On Wed, Jul 21, 2010 at 8:28 PM, Stephens, Chris <Chris.Stephens@xxxxxxx>wrote:

>  10.2.0.4 on RHEL 5
>
>
>
> Unfortunately I didn’t think to look at *_mviews prior to fixing the
> issue.  We are using a materialized view to enforce a business rule of no
> current project can have a sum(fraction) != 100.
>
>
>
> Base table:
>
>
>
> SQL> desc allocation
>
>  Name                                      Null?    Type
>
>  ----------------------------------------- --------
> ----------------------------
>
>  FRACTION                               NOT NULL NUMBER(5)
>
>  PROJECTNUMBER                  NOT NULL VARCHAR2(127)
>
>  CODE                                       NOT NULL CHAR(3)
>
>  ISCURRENT                              NOT NULL NUMBER(1)
>
>  CHANGEDATE                         NOT NULL DATE
>
>  PERIODID                                NOT NULL NUMBER(10)
>
>
>
> Materialized view:
>
>
>
> CREATE MATERIALIZED VIEW “ALLOCATIONTOTALS"  REFRESH FORCE ON COMMIT USING
> DEFAULT LOCAL ROLLBACK SEGMENT DISABLE QUERY REWRITE
>
> AS
>
>   (SELECT projectnumber,
>
>     periodid,
>
>     SUM(fraction) AS total
>
>   FROM allocation
>
>   WHERE iscurrent = 1
>
>   GROUP BY projectnumber,
>
>     periodid
>
>   );
>
>
>
> Check constraint on the mview:
>
>
>
> total = 100
>
>
>
> enabled, not deferrable, validated.
>
>
>
> A developer emailed me indicating he was able to violate this constraint
> without error.  I confirmed.  As soon as I dropped and recreated the mview
> (after cleaning up the data), I was unable to duplication the issue.
>
>
>
> Does anyone see a problem with relying on this method to enforce the rule?
> Theoretically at least.
>
>
>
> Chris
>
> CONFIDENTIALITY NOTICE:
> This message is intended for the use of the individual or entity to which
> it is addressed and may contain information that is privileged, confidential
> and exempt from disclosure under applicable law. If the reader of this
> message is not the intended recipient or the employee or agent responsible
> for delivering this message to the intended recipient, you are hereby
> notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you have received this
> communication in error, please notify us immediately by email reply.
>
>
>


-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

Other related posts: