Important research on pre-joining Oracle tables
One serious performance issue
with highly-normalized, non-redundant Oracle table designs (e.g. third normal form)
is that Oracle experiences a high degree of overhead (especially
CPU consumption) when
joining dozens of tables together, over-and-over again.
Using materialized views we can pre-join the tables together,
resulting in a single, fat, wide and highly-redundant table.
This can reduce logical I/O from tens of
thousands to a single row fetch, resulting in blisteringly fast response
time, but careful attention must be paid to choosing the proper
materialized view partition keys and the best refresh interval.
The problem with materialized view for
pre-joined tables is keeping them refreshed. Because the
materialized view is built from many tables, and changes to the base
tables require an update to the materialized view.
Partitioning of materialized views is a great
aid, provided that we choose the partition key wisely.
The following research by Mike Ault
illustrates this important technique and shows how proper partition
analyze can allow the Oracle DBA to de-normalize a schema for high
performance without affecting high volumes of concurrent users.
http://www.dba-oracle.com/t_materialized_view_refresh_method_benchmark.htm
|
