关闭

物化视图(Materialized View)优化

标签: 物化视图DBMS_ADVISORMate
235人阅读 评论(0) 收藏 举报
分类:

使用DBMS_advisroe.tune_mivew 优化物化视图

使用的用户为sh,相关的表如下:

SQL> conn sh/sh
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CHANNELS                       TABLE
CUSTOMERS                      TABLE
MV_CAPABILITIES_TABLE          TABLE
PROD_MV                        TABLE
SALES                          TABLE
TIMES                          TABLE

1、查询物化视图的建表语句;

SQL> set long 1000
SQL> select query from user_mviews where mview_name='PROD_MV';

QUERY
--------------------------------------------------------------------------------
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,SUM(s.amount_sold)
 sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE
s.time_id = t.time_id AND   s.cust_id = c.cust_id AND   s.channel_id = ch.channe
l_id GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc

2、使用advisor优化查询到的物化视图

SQL> var tn varchar2(20);
SQL> exec :tn:='tasksh';

PL/SQL procedure successfully completed.

SQL> print tn

TN
--------------------------------
tasksh
SQL> exec dbms_advisor.TUNE_MVIEW(:tn,'create materialized view prod_mv as SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND   s.cust_id = c.cust_id AND   s.channel_id = ch.channel_id GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc');</span>
PL/SQL procedure successfully completed.

3、创建目录并赋权

SQL>  create or replace directory tr as '/home/oracle/files';


Directory created.


SQL> grant all on directory tr to sh;


Grant succeeded.

4、生成优化脚本

SQL> exec dbms_advisor.create_file(dbms_advisor.get_task_script(:tn),'TR','my_tune_mview.sql');

PL/SQL procedure successfully completed.
5、查看脚本生成的脚本

Rem  SQL Access Advisor: Version 11.2.0.3.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            tasksh
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("CUST_ID","TIME_ID","CHANNEL_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("CUST_ID","TIME_ID","CHANNEL_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CHANNELS"
    WITH ROWID, SEQUENCE("CHANNEL_ID","CHANNEL_CLASS")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CHANNELS"
    ADD ROWID, SEQUENCE("CHANNEL_ID","CHANNEL_CLASS")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("CUST_ID","CUST_CITY")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID","CUST_CITY")
    INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
    "SH"."TIMES"
    WITH ROWID, SEQUENCE("TIME_ID","CALENDAR_QUARTER_DESC")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."TIMES"
    ADD ROWID, SEQUENCE("TIME_ID","CALENDAR_QUARTER_DESC")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.PROD_MV
    REFRESH FAST WITH ROWID
    DISABLE QUERY REWRITE
    AS SELECT SH.TIMES.CALENDAR_QUARTER_DESC C1, SH.CUSTOMERS.CUST_CITY C2, SH.CHANNELS.CHANNEL_CLASS
       C3, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD")
       M2, COUNT(*) M3 FROM SH.TIMES, SH.CUSTOMERS, SH.CHANNELS, SH.SALES WHERE
       SH.SALES.CHANNEL_ID = SH.CHANNELS.CHANNEL_ID AND SH.SALES.TIME_ID = SH.TIMES.TIME_ID
       AND SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.TIMES.CALENDAR_QUARTER_DESC,
       SH.CUSTOMERS.CUST_CITY, SH.CHANNELS.CHANNEL_CLASS;

6、创建优化统计信息表MV_CAPABILITIES_TABLE

SQL> @?/rdbms/admin/utlxmv.sql

Table created.

SQL> desc MV_CAPABILITIES_TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 MVOWNER                                            VARCHAR2(30)
 MVNAME                                             VARCHAR2(30)
 CAPABILITY_NAME                                    VARCHAR2(30)
 POSSIBLE                                           CHAR(1)
 RELATED_TEXT                                       VARCHAR2(2000)
 RELATED_NUM                                        NUMBER
 MSGNO                                              NUMBER(38)
 MSGTXT                                             VARCHAR2(2000)
 SEQ                                                NUMBER
7、先收集下当前的PROD_MV的信息

SQL> exec dbms_mview.explain_mview('PROD_MV',:tn);

PL/SQL procedure successfully completed.

8、删除掉之前的PROD_MV 执行生成的脚本,启用查询重写修改原脚本中DISABLE QUERY REWRITE

SQL> select count(*) from MV_CAPABILITIES_TABLE;

  COUNT(*)
----------
        26

SQL> drop materialized view prod_mv;

Materialized view dropped.

SQL> @/home/oracle/files/my_tune.sql

Materialized view log created.

Materialized view log altered.

Materialized view log created.

Materialized view log altered.

Materialized view log created.

Materialized view log altered.

Materialized view log created.

Materialized view log altered.

Materialized view created.

9、再解释下当前的物化视图

SQL>  exec dbms_mview.explain_mview('PROD_MV','PROD_NEW');


PL/SQL procedure successfully completed.


SQL> select count(*) from MV_CAPABILITIES_TABLE;


  COUNT(*)
----------
        46


生成的结果存在MV_CAPABILITIES_TABLE

最后的SH用户下的表:

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CHANNELS                       TABLE
CUSTOMERS                      TABLE
MLOG$_CHANNELS                 TABLE
MLOG$_CUSTOMERS                TABLE
MLOG$_SALES                    TABLE
MLOG$_TIMES                    TABLE
MV_CAPABILITIES_TABLE          TABLE
PROD_MV                        TABLE
SALES                          TABLE
TIMES                          TABLE

10 rows selected.












1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:29187次
    • 积分:710
    • 等级:
    • 排名:千里之外
    • 原创:41篇
    • 转载:1篇
    • 译文:1篇
    • 评论:3条
    最新评论