Materialized Views
Oracle8i introduced the concept of materialized views for the creation of summary
tables for facts and dimensions that can represent rollup levels in the hierarchies. A
materialized view provides precomputed summary data; most importantly, a
Analytics, OLAP, and Data Mining in the Database | 233
materialized view is automatically substituted for a larger detailed table when appropriate.
The cost-based query optimizer can perform query rewrites to these summary
tables and rollup levels in the hierarchy transparently, often resulting in dramatic
increases in performance. For instance, if a query can be answered by summary data
based on sales by month, the query optimizer will automatically substitute the materialized
view for the more granular table when processing the query. A query at the
quarter level might use monthly aggregates in the materialized view, selecting the
months needed for the quarter(s). Oracle Database 10g added query rewrite capabilities
such that the optimizer can make use of multiple appropriate materialized views.
Pages:
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513