les10资料参考 学习.pptVIP

Materialized Views Objectives After completing this lesson, you should be able to do the following: Discuss summary management and Oracle implementation of summaries Describe materialized views (MV) Identify the types, build modes, and refresh methods for materialized views Explain the query rewrite mechanism in Oracle Describe the significance of Oracle dimensions Lesson Agenda Summary management and materialized views Using materialized views Refresh modes Query rewrite Working with dimensions The Need for Summary Management How can you improve query response time? Use indexes. Partition your data. Implement parallel execution. What about precomputing query results? Create summaries: Materialized views Automatically rewrite SQL applications. Automatically refresh data. Using Summaries to Improve Performance Summaries are special types of aggregate views. Using summaries improves query execution time by precalculating expensive joins and aggregation operations before execution and storing results in a database table. Summaries are created using a schema object called a materialized view. Summary Management Summary management: Improves query-response time Is the key to the performance of data warehouses A summary is a table that: Stores preaggregated and prejoined data Is based on user query requirements Summary Navigation Effective use of summary tables requires summary table awareness. Methods for summary navigation: Warehouse database engine Proprietary summary-aware products Open summary-aware middleware 3rd generation language (3GL) and metadata solutions Managing Historical Summary Data in the Warehouse Summary Management Using the Oracle Database Materialized views: Store precomputed aggregates and joins. Results are stored in the database. Use query rewrite. Improve query performance. Summary Advisor: Is a collection of functions and procedures ( DBMS_ADVISOR, DBMS_OLAP packages) Helps in defining and analyzing materialized views Using Materialized Views