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.
Read more on this issue here:
http://oracle-tips.c.topica.com/maal8GhabJA8ebLGJrib/
For more details, see my book "Oracle Tuning: The Definitive Reference". It's 30% off, directly from the publisher:
http://oracle-tips.c.topica.com/maal8GhabJA8dbLGJrib/
Important research on pre-joining Oracle tables
