Rewrite problems

One of the Oracle 9.2 data warehouses my team manages uses nested materialized views for the aggregation strategy. Nesting of aggregates can reduce the workload on the batch as ‘expensive’ roll-ups only need happen once. The downside to nesting is that in order to make query re-write work for the higher-level nested tables we must use snowflake dimension tables; with Oracle 10g we are able to get around that by using rewrite equivalence.

As a routine data warehouse management process we look at long running queries (we have developed a simple Oracle SQL Developer report to spot long running BusinessObjects queries) and extract the SQL. Often we find that the user is at fault (forgetting the time predicate is just a bit too common), but recently we spotted a whole group of queries that instead of running against the materialized view that fully resolved the query they ran against the base table and rolled-up four levels of hierarchy. After a bit of head scratching we came up with a reason; the final table of the hierarchy was missing! Each table in the dimension hierarchy was joined to its parent on the foreign key, an example would be city joins to state joins to country but you would still know the country from the foreign key on the state table. Omitting that last join in the snowflake made resolving query rewrite a bit too hard for the optimiser to cope with; any query that used an attribute of the top level just failed to rewrite.

Fixing this is not a quick process. The views are nested so any operation on a lower level mview requires us to drop its children first (five of them in this case). And there is no such command as CREATE OR REPLACE MATERIALIZED VIEW. We have to drop and then recreate the mview and worse still this view was not created on a pre-built table so we will loose all of the aggregated data in the view. We have a cunning plan based on partition exchange to ‘convert’ the mview to a table and should save us the pain of having to rebuild the whole summary; we should then be able to fix the mview query to one that allows re-write