Materialized views for multi-level aggregated data

Too many materialized views

Query rewrite is a very nice feature of Oracle Database EE. It was first introduced into Oracle8i. It allows significantly to speed up a lot of data aggregating queries. However the more queries we want to tune with query rewrite, the more of materialized views (MVs) we need. And usually we end up with much too many of them. Let me show you some example. I have a simple model with 2 dimensions D_Time and D_Product joined to a F_Sales fact table. This is a regular star schema data model.

multidimensional model
Continue reading