oracle 物化视图多表合并,Oracle 物化视图1 - 单表聚合及其快速刷新

简介

物化视图在数据仓库中常用,将结果预先计算好并存储在物化视图中,Oracle数据库通过Query Rewrite访问物化视图。可以提高SQL反应速度,改善用户体验。整个过程对用户是透明的。对于每个物化视图,Oracle会自动建立一个同名表来存储物化视图的数据。

物化视图支持两种刷新方式:

Fast Refresh

Complete Refresh.

Fast refresh的好处在于增量式更新MV,而Complete Refresh需要重新执行MV的Select语句。一个数据量大的基表一次Complete Refresh比Fast Refresh需要更多时间。

物化视图的统计信息

每一个物化视图都有一个同名的表。收集物化视图的统计信息就是收集这个表的统计信息。例如,物化视图T_AGG

exec dbms_stats.gather_table_stats(,'T_AGG', cascade=>true)

聚合式物化视图Fast Refresh的条件:

1. 基表上建立MV Log,MV log必须包含:

Rowid

所有在MV中出现的列

INCLUDING NEW VALUES

Sequence

2. MV的select包含

count(*)

对于每个做了聚合的列,都必须有count()

更多条件请见:Oracle Data Warehousing Guide第九章Basic Materialized Views的Restrictions on Fast Refresh on Materialized Views with Aggregates

Refresh on commit

物化视图的刷新将作为事务的一部分,因而事务提交时间将稍微长一点。

例子

CREATE MATERIALIZED VIEW LOG ON T_fact WITH ROWID, SEQUENCE (

T_START_DATE,

Customer_key,

T_id,

serve_company,

SUCCESS_COUNT

)

INCLUDING NEW VALUES;

DROP MATERIALIZED VIEW T_agg;

CREATE MATERIALIZED VIEW T_AGG

PARALLEL

PARTITION BY RANGE(T_START_DATE) INTERVAL(NumToYMInterval(1, 'MONTH'))

SUBPARTITION BY HASH(Customer_key) SUBPARTITIONS 512

( PARTITION part_dummy VALUES LESS THAN (to_date('2014/01/01','yyyy/mm/dd')))

BUILD IMMEDIATE

REFRESH FAST ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT T_START_DATE,

Customer_key,

T_id,

serve_company,

SUM(SUCCESS_COUNT),

COUNT(SUCCESS_COUNT),

COUNT(*)

FROM T_fact

GROUP BY T_START_DATE,Customer_key,T_id,serve_company;

PL/SQL辅助包

Oracle提供了两个PL/SQL包

DBMS_MVIEW

DBMS_ADVISOR

dbms_mview.explain_mview函数分析指定MV是否支持Fast Refresh, Query Rewrite,将结果存入mv_capabilities_table.。

该表需要事先运行$ORACLE_HOME/rdbms/admin/utlxmv.sql创建。表中还会给出不支持Fast Refresh, Query Rewrite的原因。

set serveroutput on;

truncate table mv_capabilities_table;

EXEC dbms_mview.refresh('T_AGG');

exec DBMS_MVIEW.EXPLAIN_MVIEW('T_AGG');

select * from mv_capabilities_table;

dbms_advisor.tune_mview:调优MV及其Log的DDL语句,使之能支持Fast Refresh和Query Rewrite。返回一个Task Name,用户可以根据该Task Name去User_tune_mview中查询优化后的DDL。

declare

v_msg varchar2(4000);

begin

dbms_ADVISOR.TUNE_MVIEW(v_msg,

''

);

dbms_output.put_line(v_msg);

end;

Select * from user_tune_mview where task_name='';

最后,执行该表中DDL即可。

为什么Query Rewrite没有发生?

我们用一个例子说明,采用Oracle自带的SH schema,该schema预定义一个sales表和物化视图CAL_MONTH_SALES_MV。我们做一个很简单的测试:手工执行物化视图的select部分。Explain Plan已经能够感知Query Rewrite,预期的“执行计划”如下:

68b4e334262439600e558d5b0abae399.png

这里Oracle没有使用物化视图,即Query Rewrite没有发生。但是Oracle提供了一个非常有用的工具:dbms_mview.EXPLAIN_REWRITE

DBMS_MVIEW.EXPLAIN_REWRITE (

query VARCHAR2,

mv VARCHAR2(30),

statement_id VARCHAR2(30));

This procedure enables you to learn why a query failed to rewrite, or, if it rewrites, which materialized views will be used. Using the results from the procedure, you can take the appropriate action needed to

make a query rewrite if at all possible. The query specified in the EXPLAIN_REWRITE statement is never actually executed.

在这之前,运行一次$ORACLE_HOME/rdbms/admin/utlxrw.sql在当前schema创建REWRITE_TABLE。

然后我们可以运行这个存储过程来得知为什么Query Rewrite没有发生。

declare

v_sql varchar2(1000) := 'SELECT   t.calendar_month_desc

,        sum(s.amount_sold) AS dollars

FROM     sales s

,        times t

WHERE    s.time_id = t.time_id

GROUP BY t.calendar_month_desc';

begin

DBMS_MVIEW.EXPLAIN_REWRITE (v_sql,'SH.CAL_MONTH_SALES_MV', 'TEST1');

end;

select  query, rewritten_txt, message, pass from   REWRITE_TABLE where statement_id='TEST1';

9604df1156f3816318d3ea2a91629086.png

对照着这个结果,我们一个个改正就行了!里面详细内容请见下一篇

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值