Materialized views (revised.. but only a little!)

In a recent blog reply Oracle Sponge Blog Squarepants (sorry David) mentioned not having much success with query rewrite on Materialized Views. Rashly I said I would blog something. These notes relate specifically to Oracle 9.2 in Data Warehouses. You will find changes with Oracle 10

MV creation: there are two ways to create an MV, you can either create the view using a pre-built table or have the table built as part of the create MV statement (the population of this type of MV can be deferred though). For non-trivial MVs I would go for the pre-built table option. Why? Two reasons: a complete refresh (or an intial build) on a MV is one big operation, the whole table is built as a single select.... and after watching my temp exceed 200gb on the sort from hell, decided there was a better way. Also, if you ever need to redefine the MV query you will have to drop the MV - this in turn drops the storage created by the MV – but pre-built tables are left alone.

Getting query rewrite to work: 1) don’t forget to enable the view for re-write. 2) Don’t forget to allow re-write in your session. 3) Set the appropriate value for rewrite integrity. 4) Make sure you have enough stats for the optimizer to do its stuff

But for MV to work you must also present the correct facts to the optimizer. Valid Dimension objects must be defined (check for valid using OEM or the supplied DBMS_OLAP package), foreign keys must be present on the MV (use RELY, you probably won’t want real constraints), not null constraints should present. Use OEM to check the re-write capabilities of the MV. Test rewrite by looking at the execution plans of test queries.

Rewrite issues

Not all queries can re-write. This is improving as Oracle moves from 8i through 9 to 10g but some things are (and will always be) too complex to re-write, in fact the simpler the select the better – I had a query generated by Business Objects that had CASE ... ELSE ... statements in the select – CASE worked ok (the view definition was a simple select) but with ELSE clause… nothing!

Nested views may not re-write beyond the first level. Suppose you have a MV defined off another MV (perhaps to reduce build time by doing an expensive aggregate just once). What happens is the query re-writes for the first view and now presents the rewritten query for further rewrite to the next view however if you have joined to a denormalized dimension table you no longer have a one-to-on relationship to the join key (you have already rolled up one or more levels in that dimension). The solution is to use a snowflake design to ensure that the view is defined on one-to-one fact foreign key to reference table joins

Nested views aren’t always used. Suppose that you have two MVs defined off your base fact table and a third MV off one of the first tier MVs. The second level view answers the test query exactly. Does it get used? Maybe, maybe not. Here, the query tries to re-write against the two first tier views, and then try to rewrite again from the ‘best’ match. If that is not the parent of the second tier view it will not be considered for further rewrite. This is not such a problem in 10g as you can use re-write equivalence to force the view to be considered

I’ll look at fast refresh later…. bet you can't wait