数据库物化视图临时表.pptVIP

Materialized Viewsand Temporary Tables Objectives After completing this lesson, you should be able to do the following: Identify the purpose and benefits of materialized views Create materialized views Enable query rewrites Create dimensions Identify the benefits of temporary tables Materialized Views A materialized view: Is an “instantiation” of a SQL statement Has its own data segment and offers: Space management options Use of its own indexes Is useful for: Expensive and complex joins Summary and aggregate data Why Materialized Views? Why Materialized Views? How Many Materialized Views? Query rewrite chooses the materialized view to use. One materialized view per query: Ideal for queries performance Not recommended: consumes too much disk space One materialized view for multiple queries: One materialized view can be used to satisfy multiple queries. Less disk space is needed. Less time is needed for maintenance. Create Materialized Views:Syntax Options Creating a Materialized View: Example Types of Materialized Views Materialized views with aggregates Materialized views containing only joins Refresh Methods COMPLETE FAST FORCE NEVER REFRESH_METHOD in ALL_MVIEWS Refresh Modes ON DEMAND: Manual ON COMMIT: Refresh done at transaction commit. Mode can be specified for only fast-refreshable materialized views. In case of failure, subsequent refreshes are manual. Schedule: At regular intervals REFRESH_MODE in ALL_MVIEWS Refresh Materialized Views Manual refresh: By using the DBMS_MVIEW package Automatic refresh Synchronous: Upon commit of changes made to the underlying tables but independent of the committing transaction Asynchronous: Defines a refresh intervalfor the materialized view Full Notes Page Manual Refresh Using DBMS_MVIEW For ON DEMAND refresh only Three procedures with the DBMS_MVIEW package: REFRESH REFRESH_ALL_MVIEWS REFRESH_DEPENDENT Materialized Views: Manual Refresh Specific materialized views: Query Rewrites To use a materialized view instea