.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Wednesday, April 05, 2006

Bad Advice On Materialized View Deferrable Constraints

An article by Jonathan Lewis on constraint problems with materialized views set off a little bell in my head about a metalink document on the subject, Note 284101.1.

The note protests the use of unique, foreign key and primary key constraints on materialized views, and includes the following interesting phrases ...

"It is not a good idea to create unique (unique indexes ) constraints , primary key or Foreign key constraints on the snapshot base table ... There should not be any Unique/PK constraints at Mview base table ... Drop the Unique/PK constriants on Mview Base Tables."

Then one of the workarounds is "You can have Deferred Constraints". The document doesn't identify why having deferred constraints might be a bad idea, or why not having integrity constraints on the table at all is preferable to having them deferred .. I can't think of a reason why that might be so, and I can think of several reasons why it might not be so (query optimization being the prime one).

All this misdirection and ambiguity, combined with the abbreviation of an error code from "ORA-00001" to "ORA-1" in the title of a help document (does that make sense to anyone?) make this a pretty poor piece of work. It's not the Most Useless Metalink Article Ever, but it's close.

By the way, if you read the title and the first few words of this article, and thought "Dave's going to say that Jonathan Lewis gave bad advice!" before realising I wasn't ... the effect was intentional :D If you didn't think that then my plan failed.


At 11:47 PM, Anonymous OracleDoc said...

Hey Dave the link you gave for Jonathon's article is dead (at least for me).

Just thought I'd let you know :)

At 3:01 AM, Anonymous Carlos said...

"the link you gave for Jonathon's article is dead (at least for me)."

It works okay for me.




Post a Comment

<< Home