Les10-物化视图临时表.ppt

Materialized Viewsand Temporary Tables,Objectives,After completing this lesson,you should be able to do the following:Identify the purpose and benefits of materialized viewsCreate materialized viewsEnable query rewritesCreate dimensionsIdentify the benefits of temporary tables,Materialized Views,A materialized view:Is an“instantiation”of a SQL statementHas its own data segment and offers:Space management optionsUse of its own indexesIs useful for:Expensive and complex joinsSummary and aggregate data,Why Materialized Views?,SELECT c.cust_id,SUM(amount_sold)FROM sales s,customers cWHERE s.cust_id=c.cust_idGROUP BY c.cust_id;,CREATE TABLE cust_sales_sum ASSELECT c.cust_id,SUM(amount_sold)AS amount FROM sales s,customers cWHERE s.cust_id=c.cust_idGROUP BY c.cust_id;,SELECT*FROM cust_sales_sum;,Why Materialized Views?,CREATE MATERIALIZED VIEW cust_sales_mvENABLE QUERY REWRITE ASSELECT c.cust_id,SUM(amount_sold)AS amount FROM sales s,customers cWHERE s.cust_id=c.cust_idGROUP BY c.cust_id;,SELECT c.cust_id,SUM(amount_sold)FROM sales s,customers cWHERE s.cust_id=c.cust_idGROUP BY c.cust_id;,SELECT STATEMENTTABLE ACCESS(FULL)OF cust_sales_mv,How Many Materialized Views?,Query rewrite chooses the materialized view to use.One materialized view per query:Ideal for queries performanceNot recommended:consumes too much disk spaceOne 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,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 REWRITEAS 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,p.prod_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 c.prod_id=p.prod_id 13 GROUP BY t.week_ending_day 14,t.calendar_year 15,p.prod_subcategory;Materialized view created.,Types of Materialized Views,Materialized views with aggregatesMaterialized views containing only joins,CREATE MATERIALIZED VIEW cust_sales_mv ASSELECT c.cust_id,s.channel_id,SUM(amount_sold)AS amount FROM sales s,customers cWHERE s.cust_id=c.cust_idGROUP BY c.cust_id,s.channel_id;,CREATE MATERIALIZED VIEW sales_products_mv ASSELECT s.time_id,p.prod_nameFROM sales s,products pWHERE s.prod_id=p.prod_id;,Refresh Methods,COMPLETEFAST FORCENEVERREFRESH_METHOD in ALL_MVIEWS,Refresh Modes,ON DEMAND:ManualON 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 intervalsREFRESH_MODE in ALL_MVIEWS,Refresh Materialized Views,Manual refresh:By using the DBMS_MVIEW packageAutomatic refreshSynchronous:Upon commit of changes made to the underlying tables but independent of the committing transactionAsynchronous:Defines a refresh intervalfor the materialized view,Full Notes Page,Manual Refresh Using DBMS_MVIEW,For ON DEMAND refresh onlyThree procedures with the DBMS_MVIEW package:REFRESHREFRESH_ALL_MVIEWSREFRESH_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 DBMS_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 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 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|forceQUERY_REWRITE_INTEGRITY=enforced|trusted|stale_tolerated,Full Notes Pages,Query Rewrite:Example,SQL explain plan for 2 SELECT t.week_ending_day 3,t.calendar_year 4,p.prod_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 Rewrite:Example,SQL SELECT t.week_ending_day 2,t.calendar_year 3,p.prod_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 c.prod_id=p.prod_id 8 AND t.calendar_year=1999 9 GROUP BY t.week_ending_day,t.calendar_year 10,p.prod_subcategory 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 nameSQL statementAdvises what is and what is not possible:For an existing materialized viewFor a potential materialized view before you create itResults 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:Example,EXEC dbms_mview.explain_mview(cust_sales_mv,123);,SELECT capability_name,possible,related_text,msgtxtFROM mv_capabilities_tableWHERE 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 PMARKER in select listPCT_TABLE N CUSTOMERS relation is not a partitioned table.,Dimensions,Data dictionary structures Define hierarchies between pairs of column setsSuperset of referential constraints:Normalized dimensionsDenormalized dimensionsNever enforcedOptional but highly recommended because they:Enable additional query rewrites without the use of constraints Can be used by OLAP tools,Dimensions and Hierarchies,Hierarchy,Day nameMonth nameQuarter descriptionDays in quarter,Day,All,Attributes:,Year,Quarter,Month,Fiscal year,Fiscal quarter,Fiscal month,Fiscal week,Dimensions:Example 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 199802-JAN-98 1998-01 1998-Q1 199803-JAN-98 1998-01 1998-Q1 199804-JAN-98 1998-01 1998-Q1 199805-JAN-98 1998-01 1998-Q1 199806-JAN-98 1998-01 1998-Q1 1998.30-DEC-01 2001-12 2001-Q4 200131-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-CALENDAR_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 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,STATE,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,HierarchyFIS_ROLLUP,HierarchyCAL_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 sessionHave definitions that persist in the data dictionaryMake data visible to the session onlyAllocate temporary extents as neededStore data in users temporary tablespaceAre of two typesGlobalLocal,Creating Temporary Tables,SQL CREATE GLOBAL TEMPORARY TABLE sales_detail_temp 2(cust_last_name VARCHAR2(50)3,cust_income_level VARCHAR2(30)4,cust_email VARCHAR2(30)5,prod_name VARCHAR2(50)6,channel_desc VARCHAR2(20)7,promo_name VARCHAR2(20)8,amount_sold NUMBER)9 ON COMMIT PRESERVE ROWS;Table created.,Creating Temporary Tables,SQL SELECT table_name,temporary,duration 2 FROM ALL_tables 3 WHERE table_name=SALES_DETAIL_TEMP;TABLE_NAME T DURATION-SALES_DETAIL_TEMP Y SYS$SESSION,Summary,In this lesson,you should have learned how to:Create materialized viewsEnable query rewrites using materialized viewsEnable additional query rewrite capabilities with dimensions and hierarchiesCreate temporary tables to hold data within the scope of a session or transaction,