0

Is the materialized views of oracle(11g) are good practice for aggregated tables in Data warehousing?

We have DW processes that replace 2 month of data each day. Some time it means few Gigs for each month (~100K rows). On top of them are materialized views that get refreshed after night cycle of data tranfer. My question is would it be better to create aggregated tables instead of the MVs?

Gluz
  • 3,154
  • 5
  • 24
  • 35
  • It would be helpful if you can explain a bit about why you are considering replacing MVs; I assume that some issue is prompting you to look for alternatives and it would be useful for people to understand what problem you're trying to solve. – Pondlife May 16 '12 at 11:35
  • I'm not sure this is helpful. I had a case (years ago) with bad experience of MVs in Oracle 9i. The query rewrite was messed up a lot and queries took too long. I think this is a common case in almost every DW and I would like to know if someone checked it before, if it stable and give better performance than aggregate tables. – Gluz May 16 '12 at 12:09
  • Do you have poor performance and an unstable system now? If you do, then of course you should be looking into the reasons why, but on the other hand if everything is running fine then your time is probably better spent elsewhere. Solving problems before they exist is difficult :-) – Pondlife May 16 '12 at 12:48
  • Out of 4 hours of night load, 2 hours are MVs refresh. No problems than, just want to make it closer to 2 hours. – Gluz May 16 '12 at 13:27

1 Answers1

1

I think that one case where aggregated tables might be beneficial is where the aggregation can be effectively combined with the atomic-level data load, best illustrated with an example.

Let's say that you load a large volume of data into a fact table every day via a partition exchange. A materialized view refresh using partition change tracking is going to be triggered during or after the partition exchange and it's going to scan the modified partitions and apply the changes to the MV's.

It is possible that as part of the population of the table(s) that you are going to exchange with the fact table partitions you could also compute aggregates at various levels using CUBE/ROLLUP, and use multitable insert to load up tables that you can then partition exchange into one or more aggregation tables. Not only might this be inherently more efficient through avoiding rescanning the atomic-level data, your aggregates are computed prior to the fact table partition exchange so if anything goes wrong you can suspend the modification of the fact table itself.

Other thoughts might occur later ... I'll open the answer up as a community Wiki if other have ideas.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96