For a while we have used DBMS_STATS.GATHER_SCHEMA_STATS with the gather stale option to collect statistics on one of our Oracle 9.2 data warehouses. It was perhaps a little slower than our old approach but it did the job and was also code not written (and

A while back I blogged [https://www.rittmanmead.com/blog/2005/11/07/design-matters/] about a data warehouse review we are doing for a customer. At the time I was less than impressed by the consistency of the approach to DW design and the lack of knowledge of how Oracle

You may have read in a previous Blog that I am a great believer in the use of constraints (not null, primary key and, of course, foreign key) in a data warehouse. In an Oracle data warehouse these constraints inform the query optimiser about relationships between dimension and fact data.

In a recent Oraclesponge posting [http://oraclesponge.blogspot.com/2005/12/ora-12034-materialized-view-log.html] David Aldridge writes about a way to get around the “ORA-12034: materialized view log younger than last refresh” error. Of course his method relies on you knowing what it is about the MV that needs to be

For one of my DW customers we adopted a materialized view-based aggregation strategy. Only the base level summaries are exposed to the query tool and the Oracle database uses query rewrite to select the 'best fit' aggregation. This was the customers first use of query rewrite, their previous

In a recent post, David Aldridge [http://oraclesponge.blogspot.com/2005/08/execution-plans-for-partition-not.html] discussed an approach for efficiently rebuilding a table so that partition key migrated from one column to another. This is a somewhat specialised operation and one that most people need not consider (well not unless you

One of our users raised a support call with my team – a query returned the wrong results. My guys check it out and capture the explain plan… yes wrong results, and because the lower bound of a BETWEEN prediacte was being omitted. So a TAR is raised with support – the

A few weeks ago (OK, in June; time flys when you are old!) I posted a piece of time series aggregation [https://www.rittmanmead.com/blog/2005/06/21/time-series/] One technique I did not mention (and nobody reminded me of it ;-) ) was “partition joins”. This was a new

One of our customers uses Oracle materialized views and query rewrite as the key component of their summarisation strategy. The front-end query tool is Business Objects (or rather its web version) The great thing about rewrite is that the BO universe designer only needed to include the base level summaries

In a recent post David Aldridge [http://oraclesponge.blogspot.com/2005/05/inverting-myths.html] discusses an article on Oracle Myths that Mike Ault posted on searchoracle.com. One myth that got particular mention was the placing of tables and their indexes in separate tablespaces. Now David and I 'do

So, you have got those MV in place and now need to keep them up to date, well there are three options: do-it-yourself, Complete refresh and 'Fast' refresh. To me, fast is not the right name, as under the covers all of the rows affected by data change

In a recent blog reply Oracle Sponge Blog Squarepants [http://oraclesponge.blogspot.com/] (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