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:
  1. We create a materialized view that refreshes on commit,
  2. 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,
  3. 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,
  4. 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, or
    2) 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:
not exists
        (select 'a department'
         from (select distinct deptno from emp) d
         where exists 
                 (select 'a manager in d'
                  from emp e
                  where e.deptno = d.deptno and e.job = 'MANAGER')
           and not exists
                 (select 'a clerk in d'
                  from emp e
                  where e.deptno = d.deptno and e.job = 'CLERK')
        )

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

create materialized view managers_need_clerk
refresh fast on commit
as
select 'wrong' as text
from dual
where exists
        (select 'a department'
         from (select distinct deptno from emp) d
         where exists 
                 (select 'a manager in d'
                  from emp e
                  where e.deptno = d.deptno and e.job = 'MANAGER')
           and not exists
                 (select 'a clerk in d'
                  from emp e
                  where e.deptno = d.deptno and e.job = 'CLERK')
        )
/

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:

create materialized view managers_need_clerk
refresh fast on commit
as
select 'wrong' as text
from (select c.job
      from emp m
          ,emp c
      where m.job = 'MANAGER'
        and m.deptno = c.deptno (+)
        and 'CLERK' = c.job (+))
where job is NULL
/

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.

67 comments:

  1. Learning something new with every post. Thanks!

    I believe that fast-refreshable materialized views are our best approximation of assertions.

    ReplyDelete
    Replies
    1. Danilo,

      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.

      Toon

      Delete
  2. CREATE ASSERTION (and I do mean the non-naive sort) is a solved problem, imho.

    http://shark.armchair.mb.ca/~erwin

    ReplyDelete
  3. 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).

    ReplyDelete
  4. Wow. It is really the great example. Thank you.

    DevOps Training in Chennai

    ReplyDelete
  5. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.
    Best Selenium Training in Chennai | Selenium Training Institute in Chennai | Besant Technologies
    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    ReplyDelete
  6. I applaud the publication of your article on materialized view. It's a good reminder to look on the DevOps training.

    It is recommended to take DevOps training in Chennai quora

    Thank you for sharing with us the post that you have worked so hard to refine.


    DevOps training in chennai with placement | Best DevOps training in chennai | DevOps training in chennai OMR | DevOps training in chennai Velachery |DevOps training in chennai

    ReplyDelete
  7. Excellant post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.

    angularjs Training in chennai
    angularjs Training in chennai

    angularjs-Training in tambaram

    angularjs-Training in sholinganallur

    ReplyDelete
  8. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    python training in pune | python training institute in chennai | python training in Bangalore

    ReplyDelete
  9. Nice tutorial. Thanks for sharing the valuable information. it’s really helpful. Who want to learn this blog most helpful. Keep sharing on updated tutorials…
    Online DevOps Certification Course - Gangboard
    Best Devops Training institute in Chennai

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

    ReplyDelete
  11. Well done! Pleasant post! This truly helps me to discover the solutions for my inquiry. Trusting, that you will keep posting articles having heaps of valuable data. You're the best! 
    Java training in Chennai | Java training in Bangalore

    Java online training | Java training in Pune

    ReplyDelete
  12. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    rpa interview questions and answers
    automation anywhere interview questions and answers
    blueprism interview questions and answers
    uipath interview questions and answers
    rpa training in chennai

    ReplyDelete
  13. DevOps is currently a popular model currently organizations all over the world moving towards to it. Your post gave a clear idea about knowing the DevOps model and its importance.

    Good to learn about DevOps at this time.

    devops training in chennai | devops training in chennai with placement | devops training in chennai omr | devops training in velachery | devops training in chennai tambaram | devops institutes in chennai | devops certification in chennai

    ReplyDelete
  14. Amazing article. Your blog helped me to improve myself in many ways thanks for sharing this kind of wonderful informative blogs in live. I have bookmarked more article from this website. Such a nice blog you are providing ! Kindly Visit Us R Programming institutes in Chennai | R Programming Training in Chennai

    ReplyDelete
  15. Nice blog!! I really got to know many new tips by reading your blog. Thank you so much for a detailed information! It is very helpful to me. Kindly continue the work.

    TOEFL Classes in Chennai
    Best TOEFL Classes in Chennai
    TOEFL in Chennai
    TOEFL Classes near me
    Spanish Classes in Chennai
    Spanish Language Course in Chennai
    Spanish Courses in Chennai

    ReplyDelete
  16. Awwsome informative blog ,Very good information thanks for sharing such wonderful blog with us ,after long time came across such knowlegeble blog. keep sharing such informative blog with us. Aviation Courses in Chennai | Best Aviation Academy in Chennai
    Aviation Academy in Chennai | Aviation Training in Chennai | Aviation Institute in Chennai

    ReplyDelete
  17. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    Devops Training courses
    Devops Training in Bangalore
    Best Devops Training in pune
    Devops interview questions and answers

    ReplyDelete

  18. Awwsome informative blog ,Very good information thanks for sharing such wonderful blog with us ,after long time came across such knowlegeble blog. keep sharing such informative blog with us.
    Airport Management Courses in Chennai | Airport Management Training in Chennai | Diploma in Airport Management Course in Chennai | Airlines Training Chennai | Airline Academy in Chennai

    ReplyDelete
  19. This information is impressive. I am inspired with your post writing style & how continuously you describe this topic. Eagerly waiting for your new blog keep doing more.

    Franchise Business in India
    Education Franchise
    Computer Education Franchise
    Education Franchise India
    Computer Center Franchise
    Education Franchise Opportunities in India

    ReplyDelete
  20. Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information.


    rpa training in chennai |
    best rpa training in chennai
    rpa online training
    rpa course in bangalore
    rpa training in pune
    rpa training in marathahalli
    rpa training in btm

    ReplyDelete
  21. Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information.

    best rpa training in chennai
    rpa training in chennai |
    rpa online training
    rpa course in bangalore
    rpa training in pune
    rpa training in marathahalli
    rpa training in btm

    ReplyDelete
  22. Useful information.I am actual blessed to read this article.thanks for giving us this advantageous information.I acknowledge this post.and I would like bookmark this post.Thanks
    python course institute in bangalore | python Course institute in bangalore| python course institute in bangalore

    ReplyDelete
  23. Great!it is really nice blog information.after a long time i have grow through such kind of ideas.thanks for share your thoughts with us.
    Cloud computing Training centers in Bangalore
    Cloud Computing Training in Perambur
    Cloud Computing Training in Ashok Nagar

    ReplyDelete
  24. I wanted to thank you for this great blog! I really enjoying every little bit of it and I have you bookmarked to check out new stuff you post.
    Hadoop Training in Chennai
    CCNA Training in Chennai
    Big Data Training in Chennai
    Big Data Training
    CCNA course in Chennai
    CCNA Training institute in Chennai

    ReplyDelete
  25. Thanks For sharing such a wonderful Blog on RPA. This blog contains so much of data about RPA that anyone who is searching for RPA, its really helpful for them to grab this data from your blog on RPA. Again thank you so much for your blog on RPA.
    Thanks and Regards,
    blue prism training in chennai
    Best blue prism training in chennai
    blue prism training cost in chennai

    ReplyDelete
  26. Your post is really awesome. Your blog is really helpful for me to develop my skills in a right way. Thanks for sharing this unique information with us.
    - Digital Marketing course in Bangalore-Learn Digital Academy

    ReplyDelete
  27. Hey, Wow all the posts are very informative for the people who visit this site. Good work! We also have a Website. Please feel free to visit our site. Thank you for sharing.Well written article project management courses in chennai | pmp training class in chennai | pmp training fee | project management training certification

    ReplyDelete
  28. Such an excellent and interesting blog, Do post like this more with more information, This was very useful, Thank you.
    Airport management courses in chennai
    airlines training chennai
    airline academy in chennai
    Airline Courses in Chennai

    ReplyDelete