物化视图实用分析总结

最新推荐文章于 2024-06-25 23:38:05 发布

执笔画情ora

最新推荐文章于 2024-06-25 23:38:05 发布

阅读量1.6k

点赞数

分类专栏： ORACLE数据库物化视图

本文链接：https://blog.csdn.net/oradbm/article/details/97761446

版权

ORACLE数据库物化视图专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1 列存在子查询的物化视图创建，可先创建普通视图，然后根据普通视图创建物化视图。

2 物化视图可以进行分区。

1 物化视图

物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图可以基于表查询，视图和其它的物化视图。通常情况下，在复制环境下，物化视图被称为主表，在数据仓库中称为明细表。

对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。

2 物化视图日志

如果你想要采用快速刷新的话，必须建立基于主表的物化视图日志。物化视图日志可以记录主表被更新记录的主键、ROWID或对象标识或者二者全部。物化视图日志也可以包含其他列用以支持带子查询的物化视图的快速刷新。

物化视图日志表的名称为MLOG$_后面跟主体对象的名称。物化视图日志和主体对象建立在相同的用户模式下。一个物化视图日志可以支持多个基于主体对象的物化视图。

有下列几种物化视图日志：

主键：物化视图日志在记录主体对象发生变化的记录时，是根据主键定位的。

ROWID：物化视图日志在记录主体对象发生变化的记录时，是根据ROWID定位的。

对象标识：物化视图日志在记录主体对象发生变化的记录时，是根据对象标识定位的。

组合类型：物化视图日志在记录主体对象发生变化的记录时，是根据上面三种类型的组合来定位的。如果物化视图日志根据主键、ROWID和对象标识定位变化的数据，则这种物化视图日志同时支持主键、ROWID和对象物化视图，这种物化视图日志对于一个包含三种类型物化视图的环境是很有用的。组合类型物化视图日志和其他类型物化视图日志工作方式相同，只不过多记录了一、二种类型。

虽然主键物化视图日志和ROWID物化视图日志的差别很小，但是这些差别在实际使用中有着很大的影响。ROWID物化视图日志会在进行重组和截断时十分困难。例如，如果你对主表进行了重组和截断，那么ROWID物化视图必须执行完全刷新，这是因为ROWID发生了变化。

3 物化视图实践

Oracle提供以下几种不同类型的物化视图，以满足各种复制环境的需要：主键物化视图和ROWID物化视图。

主键物化视图

主键物化视图是默认的物化视图。在复制环境下，如果主键物化视图是作为物化视图组的一部分建立的，如果指定了FOR UPDATE语句，那么这个物化视图是可更新的，且这个物化视图组必须和主站点中复制组的同名。另外，可更新物化视图必须和主复制组在不同的数据库中。当修改发生后，修改的数据以行级为单位被传播，每行数据由主键确定。

主键物化视图可以包含一个子查询，因此你可以在建立物化视图时，建立所有数据的一个子集，也就是说，建立物化视图时可以只选取你需要的数据行。如果主站点中的主对象建立了物化视图日志表，那么一些包含特定类型子查询的主键物化视图仍然可以快速（增量）刷新。

SQL> create materialized view log on emp;

Materialized view log created.

SQL> create materialized view mv_emp_pk

2 build deferred

3 refresh fast

4 start with sysdate

5 next sysdate + 1/48

6 with primary key

7 as

8 select * from emp;

Materialized view created.

SQL> select * from mv_emp_pk;

no rows selected

子查询物化视图

SQL>create materialized view m_test_view

select * from gwm_tabattribute a where exists (select * from gwm_attribute b where a.gwm_ano=b.gwm_ano);

生成数据有两大选项：

Build immediate:在创建物化视图的同时根据主表生成数据，默认选项

Bulid deferred:在创建物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取：EXEC DBMS_MVIEW.Refresh('MV_name','C')，注意必须使用全量刷新，默认是增量刷新，所以这里参数必须是C，因为之前都没有生成数据，所以必须全量。

刷新方式有：complete fast force

Complete：完全刷新整个物化视图，相当于重新生成物化视图，此时即时增量刷新可用也全量刷新

Fast：当有数据更新时依照相应的规则对物化视图进行更新，该选项必须在创建有物化视图日志的情况下才能使用。

Force：当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项）

不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下。

3.1 快速刷新满足条件

在创建物化视图语句中，以下条件是所有类型的快速刷新物化视图都必须满足的条件：

物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM；(通信保障更新加上sysdate条件后无法创建fast刷新视图，可以创建视图然后修改名称然后创建对应的同义词或者视图进行条件筛选！！！！！)
物化视图不能包含对LONG和LONG RAW数据类型的引用。

只包含连接的物化视图：

1.必须满足所有快速刷新物化视图都满足的条件；
2.不能包括GROUP BY语句或聚集操作；
3.如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上；
4.如果不包含外连接，那么WHERE语句没有限制，如果包含外连接，那么WHERE语句中只能使用AND连接，并且只能使用“＝”操作。
5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。
6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。

包含聚集的物化视图：

1.必须满足所有快速刷新物化视图都满足的条件；
2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：
(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；
(2)必须指明ROWID和INCLUDING NEW VALUES；

INCLUDING Specify INCLUDING to save both new and old values in the log. If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, then you must specify INCLUDING.

EXCLUDING Specify EXCLUDING to disable the recording of new values in the log. This is the default. You can use this clause to avoid the overhead of recording new values. Do not use this clause if you have a fast-refreshable single-table materialized aggregate view defined on the master table.

(3)如果对基表的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括WITH SEQUENCE，则物化视图日子中将会包含

SEQUENCE$$，给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。

Specify SEQUENCE to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log. Sequence numbers are necessary to support fast refresh after some update scenarios.

3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；
4.必须指定COUNT(*)；
5.如果指明了除COUNT之外的聚集函数，则COUNT(expr)也必须存在；

比如：包含SUM(a)，则必须同时包含COUNT(a)。

6.如果指明了VARIANCE(expr)或STDDEV(expr)，除了COUNT(expr)外，SUM(expr)也必须指明；

Oracle推荐同时包括SUM(expr*expr)。

7.SELECT列表中必须包括所有的GROUP BY列；
8.当物化视图属于下面的某种情况，则快速刷新只支持常规DML插入和直接装载，这种类型的物化视图又称为INSERT-ONLY物化视图；

物化视图包含MIN或MAX聚集函数；

物化视图包含SUM(expr)，但是没有包括COUNT(expr)；

物化视图没有包含COUNT(*)。

注意：如果建立了这种物化视图且刷新机制是ON COMMIT的，则会存在潜在的问题。当出现了UPDATE或DELETE语句，除非手工完全刷新解决这个问题，否则物化视图至此以后都不再自动刷新，且不会报任何错误。

9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；
10.如果物化视图建立在视图或子查询上，则要求视图必须可以完全合并的。
11.如果没有外连接，则对WHERE语句没有限制。如果包含外连接，则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图，快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。
12.对于包含了ROLLUP、CUBE、GROUPING SET的物化视图必须满足下列限制条件：

SELECT语句列表中应该包含GROUPING标识符：可以是GROUP BY表达式中所有列的GROUPING_ID函数，也可以是GROUP BY表达式中每一列的GROUPING函数；

例如：GROUP BY语句为：GROUP BY CUBE(a, b)，则SELECT列表应该包括GROUPING_ID(a, b)或者GROUPING(a)和GROUPING(b)。

GROUP BY不能产生重复的GROUPING。

比如：GROUP BY a, ROLLUP(a, b)则不支持快速刷新，因为包含了重复的GROUPING：(a), (a, b), (a)。

见下例

SQL>CREATE MATERIALIZED VIEW LOG ON PRODUCTS WITH

SEQUENCE,ROWID

(PROD_ID,PROD_NAME,PROD_DESC,PROD_SUBCATEGORY,

PROD_CATEGORY,PROD_CATEGORY_DESC,PROD_WEIGHT_CLASS,

PROD_UNIT_OF_MEASURE,

PROD_PACK_SIZE,SUPPLIER_ID,PROD_STATUS,PROD_LIST_PRICE,

PROD_MIN_PRICE)

including new values;

SQL>CREATE MATERIALIZEd VIEW LOG ON SALES

WITH SEQUENCE,ROWID

(PROD_ID,CUST_ID,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,

AMOUNT_SOLD)

including new values;

SQL>CREATE MATERIALIZED VIEW PRODUCT_SALES_MV

PCTFREE 0

TABLESPACE USERS

STORAGE(INITIAL 8k NEXT 8k PCTINCREASE 0)

BUILD IMMEDIATE

REFRESH FAST

enable query rewrite

SELECT P.PROD_NAME,SUM(S.AMOUNT_SOLD) AS DOLLAR_SALES,

COUNT(*) AS CNT, COUNT(S.AMOUNT_SOLD) AS CNT_AMT

FROM SALES S,PRODUCTS P

WHERE S.PROD_ID = P.PROD_ID

group by p.prod_name;

SQL>CREATE MATERIALIZED VIEW SUM_SALES

PARALLEL

BUILD IMMEDIATE

REFRESH FAST ON COMMIT AS

SELECT S.PROD_ID,S.TIME_ID,COUNT(*) AS COUNT_GRP,

SUM(S.AMOUNT_SOLD) AS SUM_DOLLAR_SALES,

COUNT(S.AMOUNT_SOLD) AS COUNT_DOLLAR_SALES,

SUM(S.QUANTITY_SOLD) AS SUM_QUANTITY_SALES,

COUNT(S.QUANTITY_SOLD) AS COUNT_QUANTITY_SALES

FROM SALES S

group by s.prod_id,s.time_id;

包含UNION ALL的物化视图：

1.UNION ALL操作必须在查询的顶层。可以有一种情况例外：UNION ALL在第二层，而第一层的查询语句为SELECT * FROM；
2.被UNION ALL操作连接在一起的每个查询块都应该满足快速刷新的限制条件；
3.SELECT列表中必须包含一列维护列，叫做UNION ALL标识符，每个UNION ALL分支的标识符列应包含不同的常量值；
4.不支持外连接、远端数据库表和包括只允许插入的聚集物化视图定义查询；
5.不支持基于分区改变跟踪（PCT）的刷新；
6.兼容性设置应设置为9.2.0。

3.2 嵌套物化视图：

嵌套物化视图的每层都必须满足快速刷新的限制条件；

对于同时包含聚集和连接的嵌套物化视图，不支持ON COMMIT的快速刷新。

(This note describes 9i Local Aggregate Materialized View Fast Refresh Restrictions. Doc ID: Note:222843.1)

刷新时间：on demand on commit start with/ next

On demand:在需要刷新时进行刷新（人工判断）

On commit:在基表上有提交操作时，进行更新

Start with:指定首次刷新的时间（一般指定的是当前时间，不过也可以在创建物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据）

Next:刷新的周期时间

查询重写（Query Rewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。

SQL> Create materialized view MV_TEST

Refresh fast

Enable query rewrite

Select * from TEST;

ROWID物化视图

为了后向兼容性，Oracle除了默认的主键物化视图外，还支持ROWID物化视图。ROWID物化视图基于主表对象中行记录的物理标识ROWID。在复制环境，ROWID物化视图只被用在基于Oracle7版本的主对象的物化视图，它不能被用于建立基于Oracle8或更高版本主站点的物化视图。

SQL> create materialized view mv_emp_rowid

2 build immediate

3 refresh force

4 on commit

5 with rowid

6 as

7* select * from emp

Materialized view created.

删除日志：

DROP materialized view log on emp;

删除物化视图

drop materialized view mv_emp_rowid;

基本和对表的操作一致 --物化视图由于是物理真实存在的，故可以创建索引。

3.3 dbms_mview.explain_MVIEW

主要介绍dbms_mview.explain_MVIEW过程确定是否可以创建快速刷新的物化视图，如下例子：

create table region(

region_id number,

reg_desc varchar2(30),

constraint region_pk primary key(region_id)

);

create table sales (

sales_id number,

sales_amt number,

region_id number,

sales_dtt date,

constraint sales_pk primary key(sales_id),

constraint sales_fk1 foreign key(region_id) references region(region_id)

);

insert into region values (10,'East');

insert into region values (20,'West');

insert into region values (30,'Source');

insert into region values (40,'North');

insert into region values (50,'Middle');

commit;

insert into sales values(1,100,10,sysdate);

insert into sales values(2,200,20,sysdate-1);

insert into sales values(3,300,30,sysdate-2);

insert into sales values(4,400,40,sysdate-3);

commit;

create materialized view log on region with primary key;

create materialized view log on sales with primary key;

select * from MLOG$_REGION;

select * from mlog$_sales;

create materialized view mater_mv

select

A.ROWID SALES_ROWID,

B.ROWID REGION_ROWID,

a.sales_id,

b.region_id

from sales a,region b

where a.region_id=b.region_id;

call dbms_mview.refresh('mater_mv','F');

---用一个包分析为什么不能使用fast

call dbms_mview.explain_mview(mv => 'mater_mv',stmt_id => '200');

select t.mvowner, t.possible, t.msgtxt, t.related_text

from mv_capabilities_table t

where t.capability_name like '%REFRESH_FAST_AFTER%'

AND T.STATEMENT_ID = 200

ORDER BY 1;

CREATE materialized view log on region with ROWID;

CREATE materialized view log on sales with ROWID;

DROP materialized view mater_mv;

create materialized view mater_mv

select

A.ROWID SALES_ROWID,

B.ROWID REGION_ROWID,

a.sales_id,

b.region_id

from sales a,region b

where a.region_id=b.region_id;

call dbms_mview.refresh('mater_mv','F');

4 物化视图的优缺点

优点：

1，物化视图的最大的优势是可以提高性能：Oracle的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。

2，物化视图有很多方面和索引很相似

3，通过预先计算好答案存储起来，可以大大地减少机器的负载

A，更少的物理读--扫描更少的数据

B，更少的写--不用经常排序和聚集

C。减少CPU的消耗--不用对数据进行聚集计算和函数调用

D，显著地加快响应时间--在使用物化视图查询数据时(与主表相反)，将会很快的返回查询结果

缺点：

1，物化视图用于只读或者“精读”环境下工作最好，不用于联机事务处理系统(OLTP)环境，在事实表等更新时会导致物化视图行锁，从而影响系统并发性。

2，物化视图有出现无法快速刷新，导致查询数据不准确的现象

3，Rowid物化视图（创建的物化视图通常情况下有主键，rowid,和子查询视图）只有一个单一的主表，不能包括下面任何一项:

A，Distinct 或者聚合函数.

B，Group by，子查询，连接和SET操作

4，物化视图会增加对磁盘资源的需求，即需要永久分配的硬盘空间给物化视图来存储数据

5，物化视图的工作原理受一些可能的约束，比如主键，外键等。

5 应用案例

5.1 案例1

查询语句：

---查询重写：在查询基表时，判断是否能从物化视图中查询到结果，减少其他进行的聚合等操作、

Union all改成了or条件

5.2 案例2 数据库

1 创建public database link

create public database link b_mv

connect to dbonread identified by tmcc#2013

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.228.2)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = mon)

)

2 远程数据库创建物化视图日志

create materialized view log on fmdb.tf;

select count(1) from fmdb.mlog$_tf; 数据量不大，且一直在变化。

3 源端数据库创建物化视图

create materialized view mv_01

refresh fast

start with sysdate

next sysdate+ 5/17280

tablespace

select *

FROM fmdb.tfa

----总结修改sql格式，创建物化视图最好用select * FROM table —+条件格式

---添加sysdate等信息后无法进行快速刷新，不适合复杂查询

4 物化视图数据库停止更新job处理

案例2：开启已经broken的JOB

业务发现物化视图已经停止更新数据，

SELECT * FROM DBA_JOBS;

发现调用refresh函数的job已经broken掉，

开启job

execute dbms_job.run('JOB_ID');

call dbms_job.run('JOB_ID');

5.3 案例3 创建分区形式mv

使用union all形式

Explame 1 分区形式物化视图

CREATE Materialized VIEW "NMOSDB"."TF201502"

partition by range(event_time) store in （tbs01,tbs02,tbs03,TBS04,TBS05,TBS06，TBS07，TBS08，TBS09，TBS10)

(

partition p1 values less than (to_date('2015-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p2 values less than (to_date('2015-02-02 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p3 values less than (to_date('2015-02-03 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p4 values less than (to_date('2015-02-04 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p5 values less than (to_date('2015-02-05 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p6 values less than (to_date('2015-02-06 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p7 values less than (to_date('2015-02-07 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p8 values less than (to_date('2015-02-08 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p9 values less than (to_date('2015-02-09 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p10 values less than (to_date('2015-02-10 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p11 values less than (to_date('2015-02-11 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p12 values less than (to_date('2015-02-12 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p13 values less than (to_date('2015-02-13 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p14 values less than (to_date('2015-02-14 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p15 values less than (to_date('2015-02-15 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p16 values less than (to_date('2015-02-16 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p17 values less than (to_date('2015-02-17 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p18 values less than (to_date('2015-02-18 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p19 values less than (to_date('2015-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p20 values less than (to_date('2015-02-20 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p21 values less than(to_date('2015-02-21 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p22 values less than (to_date('2015-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p23 values less than (to_date('2015-02-23 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p24 values less than (to_date('2015-02-24 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p25 values less than (to_date('2015-02-25 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p26 values less than (to_date('2015-02-26 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p27 values less than (to_date('2015-02-27 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition p28 values less than(to_date('2015-02-28 00:00:00','yyyy-mm-dd hh24:mi:ss'))

)

refresh force

on demand

start with sysdate next sysdate+1/1440

with rowid

SELECT

FROM

TFA t2 where event_time>=trunc(sysdate,'MONTH') and event_time<=last_day(trunc(sysdate,'MONTH'))

;

GRANT SELECT ON "NMB"."TFA" TO "NPMDB";

GRANT INSERT ON "NMB"."TFA" TO "NPMDB";

GRANT DELETE ON "NMB"."TFA" TO "NPMDB";

GRANT SELECT ON "NMB"."TFA" TO "DBONREAD";

Explame 2

Create materialized view sale_MV COMPRESS

PARTITION BY HASH(SALES_ID)

PARTITIONS 16

REFRESH ON DEMAND COMPLETE WITH ROWID

SELECT 1 NAME FROM DUAL;

执笔画情ora

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
物化视图实用分析总结

1 列存在子查询的物化视图创建，可先创建普通视图，然后根据普通视图创建物化视图。2 物化视图可以进行分区。1 物化视图物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图可以基于表查询，视图和其它的物化视图。通常情况下，在复制环境下，物化视图被称为主表，在数据仓库中称为明细表。...
复制链接

扫一扫

专栏目录