CODEWITHSUNDEEP
oracle11gdata-warehousematerialized-views
Gluz
Gluz

Reputation: 3264

Oracle materialized views or aggregated tables in datawarehouse

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?

Upvotes: 0

Views: 1645

Answers (1)

David Aldridge
David Aldridge

Reputation: 52386

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.

Upvotes: 1

Related Questions