物化视图(Materialized View)优化

原创 2016年08月31日 14:31:01

使用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.












版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

TimesTen与Materialized View(物化视图)

今天和用户交流,用户突然问到,TimesTen可否缓存多个Oracle的表,也即将多个表Join的结果缓存? 我们先做几个实验,然后再看有什么解决方法。TimesTen数据库对于物化视图的支持先建立基...

Materialized View 物化视图实现 Oracle 表双向同步

Oracle 数据库跨库同步表有很多种方式可以实现, 比如触发器, Materialized View(MV), Stream, Goldengate 等        Materialized V...

傅老师课堂:Oracle高级应用之物化视图(materialized view)

原文地址:http://hi.baidu.com/gukeming888/blog/item/2682f69481c8237154fb9662.html 物化视图 (Materialized V...

利用Oracle物化视图优化项目查询

Oracle物理化视图小结 以下纯粹为本人在优化目录知识库查询时的一些小结,仅供参考,由于知识水平有限,欢迎提出修正意见   1简介 物化视图,它是用于预先计算并保存表连接或聚集等耗时较多的操...

用table+物化视图优化复杂语句中以%开头like特定格式字段查询

基于此复杂语句中性能是由语句中的like '%%'引

tune materialized view

oracle materialized view

  • 2008-05-26 13:08
  • 24KB
  • 下载

MATERIALIZED VIEW

转自新浪博客        Oracle的实体化视图提供了强大的功能,可以用在不同的环境中,实体化视图和表一样可以直接进行查询。实体化视图可以基于分区表,实体化视图本身也可以分区。 ...

materialized_view基础知识

  • 2012-10-23 09:41
  • 30KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)