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.