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.

About these ads

2 thoughts on “Bad Advice On Materialized View Deferrable Constraints

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

    Just thought I’d let you know :)

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

    It works okay for me.

    Cheers.

    Carlos.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s