tag:blogger.com,1999:blog-7395977411859619892.post265306192414999555..comments2024-01-30T09:21:06.579+01:00Comments on About Oracle: Fast refreshable materialized view errors, part oneRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-7395977411859619892.post-3798130263240945992010-10-12T22:22:30.847+02:002010-10-12T22:22:30.847+02:00Hi Flavio,
From your story I can't see anythi...Hi Flavio,<br /><br />From your story I can't see anything obvious that you might have forgotten. Can you post a small test case for me to reproduce the issue?<br /><br />Regards,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-80562829088113967512010-10-06T23:18:58.334+02:002010-10-06T23:18:58.334+02:00Hi,
i liked very much your test cases for MVs!
In...Hi,<br />i liked very much your test cases for MVs!<br /><br />In the last couple of days i've been trying to figure a strange case of ORA-12054, with little success so far.<br /><br />In brief:<br />i checked my create mv statement against DBMS_MVIEW.EXPLAIN_MVIEW and it is telling me that my materialized view is fast refreshable, however i can only define it as REFRESH FAST ON DEMAND, not Byte64https://www.blogger.com/profile/15629209362377395020noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-91271294298685116772009-08-04T19:50:47.169+02:002009-08-04T19:50:47.169+02:00Oh I didn't know that.
I provided a solution...Oh I didn't know that. <br /><br />I provided a solution based on mv's for enforcing business rules on stackoverflow.<br /><br />See: http://stackoverflow.com/questions/1209025/using-scripts-to-ensure-data-integrity-in-oracle/1217035#1217035 <br /><br />I mention you articles too. <br /><br />(same anonymous aka tuinstoel on stackoverflow)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-73716087023335483332009-08-02T15:26:41.998+02:002009-08-02T15:26:41.998+02:00@Anonymous: At first, it sounds like a fantastic w...@Anonymous: At first, it sounds like a fantastic way to have a business rules validated on commit time. I recommended it myself here: http://rwijk.blogspot.com/2008/08/implementing-entity-rules.html.<br /><br />However, there are serious scalability issues involved with this type of solution, so now I am not that fond of this solution. I will blog about this in my current MV series. Probably it Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-78450258368312834472009-08-02T10:56:43.747+02:002009-08-02T10:56:43.747+02:00@Rob
Do you use fast refresh mv's for ensurin...@Rob<br /><br />Do you use fast refresh mv's for ensuring data integrity? <br /><br />Like here: http://technology.amis.nl/blog/475/introducing-materialized-views-as-mechanism-for-business-rule-implementation-complex-declarative-constraints <br /><br />What do you think of stuff like this?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-33228620538933508772009-07-07T20:52:41.491+02:002009-07-07T20:52:41.491+02:00@blogje:
Even after adding a rowid column, this p...@blogje:<br /><br />Even after adding a rowid column, this produces an ORA-12054, which basically means you are out of luck in terms of knowing what went wrong. My guess is that the restriction "It cannot contain nested queries that have ANY, ALL, or NOT EXISTS." should be expanded a little more to include EXISTS.<br /><br />Regards,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-41859778765633758032009-07-07T14:55:04.061+02:002009-07-07T14:55:04.061+02:00Do you an idea on what restriction prevents me fro...Do you an idea on what restriction prevents me from creating a mv which has a exists clause, something like:<br /><br />create materialized view emp_mv<br />refresh fast on commit<br />as <br />select e1.empno<br />from myemp e1<br />where exists ( select e2.empno<br /> from myemp e2<br /> where e2.empno = e1.empno<br /> )blogjehttps://www.blogger.com/profile/14785008192037832811noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-44233045663218102042009-06-05T23:37:16.161+02:002009-06-05T23:37:16.161+02:00Randolf,
I changed my mind: I will not add the ou...Randolf,<br /><br />I changed my mind: I will not add the output to the current post, but create a new one with the mv_capabilities_table output in the near future.<br /><br />Regards,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-11453744819764557512009-06-01T22:54:22.522+02:002009-06-01T22:54:22.522+02:00Hi Randolf,
Thanks for the suggestion. It's a goo...Hi Randolf,<br /><br />Thanks for the suggestion. It's a good opportunity to get to know dbms_advisor.tune_mview. And I just discovered that dbms_mview.explain_mview sometimes does give very accurate error messages. I will add the outputs somewhere this week.<br /><br />Regards,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-10175201399897642702009-06-01T20:46:05.039+02:002009-06-01T20:46:05.039+02:00Rob,
it would be interesting to see the correspon...Rob,<br /><br />it would be interesting to see the corresponding outputs of DBMS_MVIEW.EXPLAIN_MVIEW and/or DBMS_ADVISOR.TUNE_MVIEW for the restriction cases.<br /><br />Regards,<br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.com