Les10-物化视图临时表.ppt

1、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 Vie

2、ws,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?,SELECT c.cust_id, SUM(amount_sold) FROM sales s, custome

3、rs c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id;,CREATE TABLE cust_sales_sum AS SELECT c.cust_id, SUM(amount_sold) AS amount FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id;,SELECT * FROM cust_sales_sum;,Why Materialized Views?,CREATE MATERIALIZED VIEW cust_sales_mv ENAB

4、LE QUERY REWRITE AS SELECT c.cust_id, SUM(amount_sold) AS amount FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id;,SELECT c.cust_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id;,SELECT STATEMENT TABLE ACCESS (FULL) OF cust_sales_m

5、v,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. L

6、ess disk space is needed. Less time is needed for maintenance.,Create Materialized Views:Syntax Options,CREATE MATERIALIZED VIEW mview_name TABLESPACE ts_name PARALLEL (DEGREE n) BUILD IMMEDIATE|DEFERRED REFRESH FAST|COMPLETE|FORCE ON COMMIT|ON DEMAND | NEVER REFRESH ENABLE|DISABLE QUERY REWRITE AS

7、SELECT FROM ,Creating a Materialized View: Example,SQL CREATE MATERIALIZED VIEW 2 cost_per_year_mv 3 AS 4 SELECT t.week_ending_day 5 , t.calendar_year 6 , d_subcategory 7 , sum(c.unit_cost) AS dollars 8 FROM costs c 9 , times t 10 , products p 11 WHERE c.time_id = t.time_id 12 AND d_id = p

8、.prod_id 13 GROUP BY t.week_ending_day 14 , t.calendar_year 15 , d_subcategory; Materialized view created.,Types of Materialized Views,Materialized views with aggregates Materialized views containing only joins,CREATE MATERIALIZED VIEW cust_sales_mv AS SELECT c.cust_id, s.channel_id, SUM(amount

9、_sold) AS amount FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id, s.channel_id;,CREATE MATERIALIZED VIEW sales_products_mv AS SELECT s.time_id, d_name FROM sales s, products p WHERE d_id = d_id;,Refresh Methods,COMPLETE FAST FORCE NEVER REFRESH_METHOD in ALL_M

10、VIEWS,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 re

11、fresh: 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 ref

12、resh only Three procedures with the DBMS_MVIEW package: REFRESH REFRESH_ALL_MVIEWS REFRESH_DEPENDENT,Materialized Views: Manual Refresh,Specific materialized views:,Exec DBMS_MVIEW.REFRESH(cust_sales_MV);,VARIABLE fail NUMBER; exec DBMS_MVIEW.REFRESH_ALL_MVIEWS(:fail);,VARIABLE fail NUMBER; exec DBM

13、S_MVIEW.REFRESH_DEPENDENT(- :fail,CUSTOMERS,SALES);,Materialized views based on one or more tables:,All materialized views due for refresh:,Query Rewrites,To use a materialized view instead of the base tables, a query must be rewritten. Query rewrites are transparent to applications. Query rewrites

14、do not require special privileges on the materialized view. A materialized view can be enabled or disabled for query rewrites.,Query Rewrites,Use EXPLAIN PLAN or AUTOTRACE to verify that query rewrites occur. Check the query response: Fewer blocks are accessed. Response time should be significantly

15、better.,Enabling and Controlling Query Rewrites,Query rewrites are available with cost-based optimization only. Optimizer hints to influence query rewrites areREWRITE and NOREWRITE.,QUERY_REWRITE_ENABLED = true|false|force QUERY_REWRITE_INTEGRITY = enforced|trusted|stale_tolerated,Full Notes Pages,Q

16、uery Rewrite: Example,SQL explain plan for 2 SELECT t.week_ending_day 3 , t.calendar_year 4 , d_subcategory 5 , sum(c.unit_cost) AS dollars 6 FROM costs c 7 , times t 8 , products p 9 WHERE c.time_id = t.time_id .,OPERATION NAME - - SELECT STATEMENT TABLE ACCESS FULL cost_per_year_mv,Query Rewr

17、ite: Example,SQL SELECT t.week_ending_day 2 , t.calendar_year 3 , d_subcategory 4 , sum(c.unit_cost) AS dollars 5 FROM costs c, times t, products p 6 WHERE c.time_id = t.time_id 7 AND d_id = d_id 8 AND t.calendar_year = 1999 9 GROUP BY t.week_ending_day, t.calendar_year 10 , d_su

18、bcategory 11 HAVING sum(c.unit_cost) 10000;,SQL SELECT week_ending_day 2 , prod_subcategory 3 , dollars 4 from cost_per_year_mv 5 where calendar_year = 1999 6 and dollars 10000;,Explain Materialized View,DBMS_MVIEW.EXPLAIN_MVIEW accepts a: Materialized view name SQL statement Advises what is and wha

19、t is not possible: For an existing materialized view For a potential materialized view before you create it Results are stored in MV_CAPABILITIES_TABLE (relational table) or in a VARRAY. utlxmv.sql must be executed as the current user to create MV_CAPABILITIES_TABLE.,Explain Materialized View: Examp

20、le,EXEC dbms_mview.explain_mview ( cust_sales_mv, 123);,SELECT capability_name, possible, related_text,msgtxt FROM mv_capabilities_table WHERE statement_id = 123 ORDER BY seq;,CAPABILITY_NAME P RELATED_TE MSGTXT- - - -.REFRESH_COMPLETE YREFRESH_FAST NREWRITE NPCT_TABLE N SALES no partition key or PM

21、ARKER in select listPCT_TABLE N CUSTOMERS relation is not a partitioned table .,Dimensions,Data dictionary structures Define hierarchies between pairs of column sets Superset of referential constraints: Normalized dimensions Denormalized dimensions Never enforced Optional but highly recommended beca

22、use they: Enable additional query rewrites without the use of constraints Can be used by OLAP tools,Dimensions and Hierarchies,Hierarchy,Day name Month name Quarter description Days in quarter,Day,All,Attributes:,Year,Quarter,Month,Fiscal year,Fiscal quarter,Fiscal month,Fiscal week,Dimensions: Exam

23、ple Table,SQL SELECT time_id day 2 , calendar_month_desc month 3 , calendar_quarter_desc quarter 4 , calendar_year year 5 FROM times;,DAY MONTH QUARTER YEAR - - - - 01-JAN-98 1998-01 1998-Q1 1998 02-JAN-98 1998-01 1998-Q1 1998 03-JAN-98 1998-01 1998-Q1 1998 04-JAN-98 1998-01 1998-Q1 1998 05-JAN-98 1

24、998-01 1998-Q1 1998 06-JAN-98 1998-01 1998-Q1 1998 . 30-DEC-01 2001-12 2001-Q4 2001 31-DEC-01 2001-12 2001-Q4 2001,Dimensions and Hierarchies,- YEAR - QUARTER - MONTH - DAY,TIMES table columns,TIMES_DIM dimension,- DAY_NAME - CALENDAR_MONTH_NAME - DAYS_IN_CAL_QUARTER,Attributes,- CALENDAR_YEAR - CAL

25、ENDAR_QUARTER_DESC - CALENDAR_MONTH_DESC - TIME_ID,Creating Dimensions and Hierarchies,SQL CREATE DIMENSION times_dim 2 LEVEL day IS TIMES.TIME_ID 3 LEVEL month IS TIMES.CALENDAR_MONTH_DESC 4 LEVEL quarter IS TIMES.CALENDAR_QUARTER_DESC 5 LEVEL year IS TIMES.CALENDAR_YEAR 6 HIERARCHY cal_rollup ( 7

26、day CHILD OF 8 month CHILD OF 9 quarter CHILD OF 10 year 11 ) 12 ATTRIBUTE day DETERMINES (day_name) 13 ATTRIBUTE month DETERMINES (calendar_month_name) 14 ATTRIBUTE quarter DETERMINES 15 (days_in_cal_quarter);,Dimensions Based on Multiple Tables,Example: A GEOGRAPHY dimension is based on CITY, STAT

27、E, and REGION tables. Define foreign key constraints on the child tables to maintain dimension validity. All attributes and keys for one level must belong to the same table.,Dimensions with Multiple Hierarchies,Hierarchy FIS_ROLLUP,Hierarchy CAL_ROLLUP,=,Day,All,Year,Month,Fiscal year,Fiscal quarter,Fiscal month,Fiscal week,Quarter,Temporary Tables,Retain data for the duration of a transaction or session Have definitions that persist in the data dictionary Make data visible to the