Within the Oracle community, there is a general consensus that database triggers are to be considered harmful.
I find this general consensus harmful...

Friday, March 8, 2013

The materialized view approach for implementing a table constraint

In yesterdays post I announced that I'd spent a separate post on how we can use materialized views to enforce table constraints. So here goes.

The high-level cookbook for this approach is as follows:

We create a materialized view that refreshes on commit,

The materialized view is defined in such a manner that it will hold no rows when the table constraint is adhered to by the current transaction trying to commit,

And it is defined such that it will hold (at least) one row when the table constraint is violated by the current transaction trying to commit,

We devise a construct such that on-commit refresh of the materialized view *always* fails whenever (at least) one row is materialized in the view. This can be done in two manners:1) we add a check constraint on the underlying table of the materialized view that always fails, or2) we add a before-insert row-trigger on the underlying table of the materialized view that always fails.

Here's the slide on this from my 'harmful triggers' presentation:

So let's try this with our example constraint (managers require a clerk in same department). The assertion for this constraint was:

With this assertion we can now mechanically generate a materialized view for our constraint, using the DUAL table. Note: we negate the assertion so that the materialized view ends up having characteristics 2 and 3 from our cookbook above. So the 'not exists' turns into an 'exists'.

Note: we explicitly want this materialized view to be "fast refreshable", meaning that Oracle will use intelligence to minimize the work required to refresh this view. In order for Oracle to be able to do so, we would also need to create a materialized view log on the table involved, which is EMP (and DUAL?) in this case. Creating the MV-log is left for the reader.

And finally we add a CHECK clause to the underlying table segment of this materialized view (whose name is the same as the materialized view). This CHECK clause is such that it always evaluates to FALSE.

alter table managers_need_clerk add CHECK( 0 = 1 )
/

The way this now should work is that whenever a transaction introduces a department that has a manager, but no clerk, and tries to commit, this on-commit refresh materialized view will produce a single row to be inserted into the underlying table segment. This triggers validation of our CHECK clause, which will always fail. Which in turn causes the commit to fail, thereby preventing this transaction to successfully complete.

All seems very well, until you now hit the following error:

ORA-12052: cannot fast refresh materialized view [owner].[mat.view]

There are still various restrictions imposed upon materialized views for them to be fast refreshable. See the Oracle documentation for this. Sometimes you might be surprised though that a bit of rewriting a materialized view could end up such that the materialized view becomes fast refreshable. Rewriting them into straight joins is a strategy that might work here. For instance our materialized view above can be rewritten into this:

I haven't tested above alternative: with the appropriate materialized view logs, it could well be fast refreshable now...

On final comment on this approach for implementing table constraints: Oracle (must and) will serialize refreshes of the materialized view among simultaneously executing (or rather, committing) transactions. Rob van Wijk has a nice blogpost on this here.

Yes they are. Albeit that their design criteria are slightly different. One could say (talking awfully informal now) that the MV has an 'ALL_ROWS' design criterium. Whereas when an MV is used to mimic an assertion, all we care about is a 'FIRST_ROWS_1' design criterium.

Other than that, MV-maintenance and assertion-maintenance are in essence the exact same problem.

There are no restrictions to the MV approach if you learn to stack them. Ie; break down a single complex one into two or more; the latter ones referencing earlier ones sequentially.

Additionally, let me introduce you to a new technique I've never seen described anywhere, and that is you can stick triggers on the MV itself! Make the MV simple (SELECT ID FROM table-name), and put all the complex stuff in the trigger(s) on the MV - row or statement.

Downside is large tables will end up with large MVs too, so perhaps a combination of slightly more complex than simple MV (to reduce the no. of rows in it), combined with triggers on the MV is the best solution in this case.

I use this method if I want to record all erroneous records in a commit elsewhere, for error reporting for example (coz many rows can be broken with any one commit, and it's nice to be able to know (and record) which ones).

Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. R Programming Course Fees

Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.