Pages

Saturday, May 30, 2009

Never put more than one error condition into one error message. It's just so common sense, I didn't even put it in the latest version of Standards and Guidelines for application developers anymore. Maybe wrongly so, because I see violations of this rule a little too often.

An example of what I mean: we have a company mail server that requires us to change our passwords every month. And when entering a new password, several conditions are checked, but they all lead to one error message: "Invalid password". You are left guessing what exactly was wrong with your last entered new password. Only by experiment you can become aware of the several error conditions, like:

Password must be more than six characters long.

Password must contain at least one upper case character.

Password must contain at least one lower case character.

Password must contain at least one special character.

Password must be different than your last six passwords.

Of course it would be very convenient if one of the above five sentences appeared as the error message. With only a little extra effort, you can make your code much more user friendly. Just introduce an error message per error condition.

Now if only Oracle did this in their fast (read: incremental) refreshable materialized view functionality, my working life for the last two months would have been so much easier. Fortunately, the documentation about materialized views is quite good, so this compensates a little.

There was also a second annoyance. I encountered situations where I did not comply with all documented restrictions. I wasn't told that I did something wrong: the materialized view was successfully created. Only when testing the software, it just didn't work. No error message, just the on commit refresh MV didn't refresh at all.

I could rant much more about this. In fact, people in my vicinity know I already did quite a lot lately. But instead I've decided to do something a little more constructive: write a few blog posts explaining possible causes for fast refresh MV related error messages. I am setting up a situation violating one of the restrictions and show the error message, if any. This way I can collect all situations where a same error message is given, and then give all situations for each error message.

In this first post I'll address some general MV error conditions. In later posts I will address issues with join MV's, aggregate MV's, union all MV's and nested MV's. After that I have a post planned summarizing the error messages and possible causes, the other way around. Hopefully people coming here by google will find some solution for solving their MV related errors. I'll end the series with a post about performance of fast refreshable MV's.

So the rest of this post I'll discuss the general restrictions for fast refresh. According to the Oracle documentation, they are:

The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.

The materialized view must not contain references to RAW or LONG RAW data types.

I tested all, except number 6, which I'll address when discussing aggregate MV errors, number 9 because I don't have that many databases here on my laptop, and number 11, which I'll postpone when discussing nested MV's.

First a simple materialized view, no joins, aggregates or union alls, on a table which is an exact copy of the EMP table:

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.

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.

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

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 will be part 7 or 8.

In the last couple of days i've been trying to figure a strange case of ORA-12054, with little success so far.

In brief: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 ON COMMIT. I checked the various restrictions in the docs but i can't check on metalink as i work on XE so i haven't got access to the support knowledgebase.

My master table is a normal heap table (so not IOT), it contains an XMLTYPE column and two URITYPE columns that i do not reference in the MV.The materialized view is a simple aggregate on one column with a COUNT(*), in the same schema, so no db links or extra object privileges are involved.I granted the user the ON COMMIT REFRESH privilege too.I tried virtually any combination of rowid, primary key and filter columns in the definition of the mview log.

The nice part is that if clone the master table using a CREATE AS SELECT * FROM master, i can create the fast refreshable on commit materialized view that fails on the original one.

This happens on both test and production system, so i am really wondering if both these instances have some bogus master table definition or if i am missing some of those restrictions mentioned in the metalink doc that i can't read.

As far as i can see the only difference between the original master table and its clone are the constraints defined on another table having a foreign key referencing the primary key and a couple of triggers.