物化视图(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.












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

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

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

TimesTen与Materialized View(物化视图)

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

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

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

Oracle高级应用之物化视图(materialized view)

物化视图 (Materialized View),在以前的Oracle版本中称为快照(Snapshot)。Oracle的物化视图提供了强大的功能,可以用于预先计算并保存表连接或聚集等耗时较多的操作的结...

基于主键创建物化视图(MATERIALIZED VIEW)

物化视图在同步数据方案中处于中低级的方案,但对有有些针对报表统计的分析系统,物化视图通过提前生成实际的物理数据,可以为报表统计提供更加快捷的查询效率,能够配合高效的快速刷新模式,它还是一种非常好的解决...
  • weeknd
  • weeknd
  • 2017年06月30日 12:10
  • 135

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

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

CREATE MATERIALIZED VIEW 语句--实现化视图创建

转自http://blog.sina.com.cn/s/blog_4f925fc3010184n4.html 此语句用于创建实现化视图 (Materialized View)。 语法 ...

SQL 优化之 oracle物化视图

  • 2010年07月28日 16:23
  • 58KB
  • 下载

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

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

SQL优化(索引、物化视图、分区)

一般sql优化有几种解决方案: 一、索引 二、分区 三、物化视图 四、并行查询 一、索引 索引的说明:    索引是与表相关的一个可选结构,在逻辑上和物理上都独立于表的数据,索引能优化查询,不能...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:物化视图(Materialized View)优化
举报原因:
原因补充:

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