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.

157 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. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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

  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. I am obliged to you for sharing this piece of information here and updating us with your resourceful guidance. Hope this might benefit many learners. Keep sharing this gainful articles and continue updating for us.
    honor service centres in chennai
    honor service center velachery
    honor service center in vadapalani

    ReplyDelete
  17. Enjoyed your approach to explaining how it works, hope to see more blog posts from you. thank you!

    Guest posting sites
    Education

    ReplyDelete
  18. Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
    python training in bangalore

    ReplyDelete
  19. Nice Post! Thank you for sharing knowledge, it was very good post to update my knowledge and improve my skills. keep blogging.
    Java Training in Electronic City

    ReplyDelete
  20. Great Article, I'am Happy to read the whole content of this blog and am very excited.Thanks for sharing these useful information. Keep updating this information!! Machine Learning Course

    ReplyDelete
  21. Nice Post...I have learn some new information.thanks for sharing.
    Data Science courses

    ReplyDelete

  22. thank you so much for this nice information Article, Digitahanks for sharing your post with us.WEBMETHODS training in bangalore

    ReplyDelete
  23. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledgez. IMSBI Training in Bangalore

    ReplyDelete
  24. Really i appreciate the effort you made to share the knowledge. The topic here i found was really effective...

    Upgrade your career Learn Oracle Training from industry experts gets complete hands on Training, Interview preparation, and Job Assistance at My Training Bangalore.

    ReplyDelete
  25. This is so elegant and logical and clearly explained. Brilliantly goes through what could be a complex process and makes it obvious.

    sap bi course

    ReplyDelete
  26. Thank for this blog are more informative contents step by step. I here attached my site would you see this blog.

    7 tips to start a career in digital marketing

    “Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

    we have offered to the advanced syllabus course digital marketing for available join now.

    more details click the link now.

    https://www.webdschool.com/digital-marketing-course-in-chennai.html

    ReplyDelete
  27. Amazing article useful information.

    Web designing trends in 2020

    When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, Whats App, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.

    we have offered to the advanced syllabus course web design and development for available join now.

    more details click the link now.

    https://www.webdschool.com/web-development-course-in-chennai.html

    ReplyDelete
  28. I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic...

    sapui5 tutorial

    ReplyDelete

  29. Cool stuff you have and you keep overhaul every one of us.
    digital marketing course pune

    ReplyDelete
  30. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    digital marketing course pune

    ReplyDelete
  31. It’s good to check this kind of website. I think I would so much from you. ExcelR Machine Learning Courses In Pune

    ReplyDelete
  32. Hi, Thanks for sharing Nice articles, are you guys done a great job...

    AI Training In Hyderabad

    ReplyDelete
  33. Effective blog with a lot of information. I just Shared you the link below for Courses .They really provide good level of training and Placement,I just Had Data Science Classes in this institute , Just Check This Link You can get it more information about the Data Science course.


    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  34. Thanks for sharing this informations.
    CCNA Training Institute in Coimbatore

    CCNA Course in Coimbatore

    Java training in coimbatore

    Selenium Training in Coimbatore

    ios training in coimbatore

    aws training in coimbatore

    big data training in coimbatore

    hadoop training in coimbatore

    ReplyDelete
  35. This material makes for great reading. It's full of useful information that's interesting,well-presented and easy to understand. I like articles that are well done.
    Best Data Science training in Mumbai

    Data Science training in Mumbai

    ReplyDelete
  36. Thanks for your post. This is excellent information. The list of your blogs is very helpful for those who want to learn, It is amazing!!! You have been helping many application.
    AWS training in chennai | AWS training in anna nagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

    ReplyDelete
  37. Wonderful post, i loved reading it.
    Share more
    Bluecoinsapp
    Otomachines
    Fairvote

    ReplyDelete
  38. thank for sharing nice information....
    more : https://www.kellytechno.com/Hyderabad/Course/amazon-web-services-training

    ReplyDelete
  39. I have express a few of the articles on your website now, and I really like your style of blogging. I added it to my favorite’s blog site list and will be checking back soon…
    More Info of Machine Learning

    ReplyDelete

  40. Very nice job... Thanks for sharing this amazing ExcelR Machine Learning Course and educative blog post!

    ReplyDelete
  41. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

    Salesforce CRM Online Training

    Salesforce CRM Classes Online

    Salesforce CRM Training Online

    Online Salesforce CRM Course

    Salesforce CRM Course Online

    ReplyDelete
  42. Very nice post..After reading your post,thanks for taking the time to discuss this, I feel happy about and I love learning more about this topic.
    AWS training in Chennai

    AWS Online Training in Chennai

    AWS training in Bangalore

    AWS training in Hyderabad

    AWS training in Coimbatore

    AWS training


    ReplyDelete
  43. I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up

    Devops Training in USA

    Hadoop Training in Hyderabad

    Python Training in Hyderabad

    ReplyDelete
  44. You have absolutely covered all the information in this content. I'm highly impressed with the quality of the content which you have written. Hope to read some more interesting articles.
    SAP training in Mumbai
    SAP course in Mumbai

    ReplyDelete
  45. You have absolutely covered all the information in this content. I'm highly impressed with the quality of the content which you have written. Hope to read some more interesting articles.
    SAP training in Mumbai
    SAP course in Mumbai

    ReplyDelete
  46. Such intense article like this one bounds the readers to enlarge their thinking power as they go through the article. I must say great research has been done before writing.
    SAP training in Kolkata
    SAP course in kolkata

    ReplyDelete
  47. Ah,so beautiful and wonderful post!An opportunity to read a fantastic and imaginary blogs.It gives me lots of pleasure and interest.Thanks for sharing.
    Data Science Training In Chennai

    Data Science Course In Chennai


    ReplyDelete

  48. I am sure that this is going to help a lot of individuals. Keep up the good work. It is highly convincing and I enjoyed going through the entire blog.
    Data Science Training

    ReplyDelete
  49. Nice & Informative Blog !
    Directly place a call at our QuickBooks Customer Service Phone Number 1-855-550-7546, for instant help.Our experts are well-trained & highly-qualified technicians having years of experience in handling user’s complexities.

    ReplyDelete
  50. Recently we are you using online market place for selling our products. Quikads is such a platform where you can sell your Second hand mobile BD & other products easily.

    ReplyDelete
  51. Nice & Informative Blog !
    QuickBooks Error 15222 mainly occurs while upgrading QuickBooks Desktop or Payroll. If you find so, fix it by dialling our Qb experts.

    ReplyDelete
  52. Nice & Infrormative Blog !
    Our team at QuickBooks Customer Service Number makes sure to give reliable service for QuickBooks in an unprecedented challenge.

    ReplyDelete
  53. Looking for best English to Tamil Typing online, make use of our site to enjoy Tamil typing and directly share on your social media handle. Tamil typing software free download

    ReplyDelete
  54. Thanks for the Valuable information.Really useful information. Thank you so much for sharing. It will help everyone.

    SASVBA Provides the Data analytics course in Delhi with the Latest Development Environment and Structures. We keep Our Programs Up to Date with the Latest modern trends. SASVBA Is One of the best education Data Analytics courses in Delhi Which Helps Learners Crack Interviews in Tech Giants.
    FOR MORE INFO:

    ReplyDelete
  55. Well we really like to visit this site, many useful information we can get here.
    Best Data Science courses in Hyderabad

    ReplyDelete
  56. I see some amazingly important and kept up to length of your strength searching for in your on the site
    Best Data Science courses in Hyderabad


    ReplyDelete
  57. What a really awesome post this is. Truly, one of the best posts I've ever witnessed to see in my whole life. Wow, just keep it up.
    data science course

    ReplyDelete
  58. This blog was very nicely formatted; it maintained a flow from the first word to the last. xo jacket

    ReplyDelete


  59. This post is so interactive and informative.keep update more information...
    Java Training in Bangalore
    Java Classes in Pune

    ReplyDelete
  60. Nice knowledge gaining article. This post is really the best on this valuable topic.
    data scientist training and placement

    ReplyDelete
  61. Pull- up your socks and knot your tie. Gonna have a good salary package job after completing Big-data Hadoop training in Chennai at Infycle. Infylce is completely for Software training and placement by friendly trainees, good atmosphere, 200% practical classes, and more.

    ReplyDelete
  62. I would like to thank you for the efforts you have made in writing this article. I am hoping for the same best work from you in the future as well..
    best digital marketing course in hyderabad

    ReplyDelete
  63. I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it..
    cloud computing in hyderabad

    ReplyDelete
  64. I think this is often an associate informative post, and it's constructive and knowledgeable. So, therefore, I'd prefer to thank you for the efforts you have created in penning this blog. Visit Website: Rcedutalent

    ReplyDelete
  65. Infycle Technologies, the best software training institute in Chennai offers the best AWS training in Chennai for tech professionals. Apart from the AWS Course, other courses such as Oracle, Java, Hadoop, Digital Marketing, Selenium, Big Data Android, and iOS Development, DevOps and Azure will also be trained with 100% hands-on training. Dial 7502633633 to get more info and a free demo

    .

    ReplyDelete
  66. This is really great informative blog. Keep sharing.I want to share about Agro Fertilizer Company in India

    ReplyDelete
  67. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.. data science training in kanpur

    ReplyDelete
  68. Top 100 titanium money clip in Canada - TITanium Art
    Shop TITanium Art titanium granite countertops from TITaniumArt now. Check ford fusion hybrid titanium out our online store how strong is titanium to find your does titanium have nickel in it latest product. Browse our huge selection of new and used pieces $19.99 keith titanium · ‎In stock

    ReplyDelete
  69. Very Informative post. Thank you for sharing with us.
    http://harmfultriggers.blogspot.com/2013/03/the-materialized-view-approach-for.html

    ReplyDelete
  70. I have read your excellent post. This is a great job. I enjoyed reading your post for the first time. I want to say thanks for this post. Thank you...
    data science training in hyderabad

    ReplyDelete
  71. Thanks for the informative and helpful post, obviously in your blog everything is good..
    cyber security course

    ReplyDelete
  72. Best AWS Training provided by Vepsun in Bangalore for the last 12 years. Our Trainer has more than 20+ Years
    of IT Experience in teaching Virtualization and Cloud topics.. we are very delighted to say that Vepsun is
    the Top AWS cloud training Provider in Bangalore. We provide the best atmosphere for our students to learn.
    Our Trainers have great experience and are highly skilled in IT Professionals. AWS is an evolving cloud
    computing platform provided by Amazon with a combination of IT services. It includes a mixture of
    infrastructure as service and packaged software as service offerings and also automation. We have trained
    more than 10000 students in AWS cloud and our trainer Sameer has been awarded as the best Citrix and Cloud
    trainer in india.

    ReplyDelete
  73. This comment has been removed by the author.

    ReplyDelete
  74. This article offers a unique perspective on the use of materialized views to enforce table constraints, challenging the prevailing notion. It's a thought-provoking read for Oracle enthusiasts. Thank you.
    Data Analytics Courses in Nashik

    ReplyDelete
  75. This paper challenges the conventional wisdom on the usage of materialised views to enforce table constraints. For Oracle fans, it's a thought-provoking read. I'm grateful.
    Data Analytics Courses in Agra

    ReplyDelete
  76. thank you so much for giving your view on implementing the table contraint

    ReplyDelete
  77. good blog
    Data Analytics Courses In Vadodara

    ReplyDelete
  78. I appreciate your balanced approach, where you acknowledge the situations where triggers can be beneficial but also emphasize the importance of careful consideration.
    Digital marketing courses in illinois

    ReplyDelete
  79. Thanks for sharing comprehensive and informative guide on implementing a table constraint.
    Digital Marketing Courses in Italy

    ReplyDelete
  80. This piece presents a distinctive viewpoint regarding the implementation of materialized views for upholding table constraints, diverging from conventional wisdom. It provides a thought-provoking and insightful read, particularly engaging for Oracle enthusiasts. Appreciate your contribution.Full Stack Java Developer Course In Marathahalli

    ReplyDelete