Without materialized views you may see unnecessary
repeating large-table full-table scans, as summaries are computed,
over and over:
David Aldridge,
a well-respected data warehouse consultant, notes that
materialized view refreshes are a one-size-fits-all solution and
that a customized solution may run many times faster:
I just reduced my client's refresh
time on 7 materialized view's of a major fact table from two
hours to 6 minutes by abandoning oracle's refresh code in
favour of my own.
The thing about materialized view
refreshes is that they are pretty much inefficient all
round, being a "one-size-fits-all" solution. The partition
change tracking-based fast refresh may not be as inefficient
as other methods, particularly when the materialized view
and the master table have exactly the same partitioning
scheme, but if you ever get a refresh failure then you can't
use PCTFR again on that materialized view until you have
performed a complete refresh on it.
The problem is that an materialized view "fast" refresh uses
a merge statement and joins the aggregated change data to
the materialized view on a function of the join columns (sys_op_map_nonnull)
so ...
i) you can't get partition
pruning on the materialized view data.
ii) you have an otherwise-useless large composite index
on the materialized view.
iii) you generally get a nested loop join instead of a
nice hash join.
iv) you sometimes end up merging when an insert would be
sufficient.
Here's what I've done myself in these situations ...
i) create the materialized view on a prebuilt table
ii) drop the materialized view at refresh time
iii) use my own merge or insert statement to load the
materialized view
iv) recreate the materialized view
In 10g you don't need the materialized view at all, you use
the DBMS advanced rewrite package instead. This is a lot
more manageable.
You can also use "tricks" like ...
i) rolling up to multiple levels of materialized view data
using a HOLAP query and then pushing the aggregated results
into the summary tables.
ii) Looking for scenarios where you can insert into the
materialized view without needing the merge.
iii) Leveraging some level of aggregation for materialized
view1 to reduce the resources required to produce
materialized view2 etc
