materialized view 物化视图-data warehousing

最新推荐文章于 2022-11-27 17:48:55 发布

AmberSheng

最新推荐文章于 2022-11-27 17:48:55 发布

阅读量948

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/shengsummer/article/details/44781733

版权

oracle 专栏收录该内容

36 篇文章 0 订阅

订阅专栏

物化视图概述：

物化视图在数据库中有两种使用场景，一种是用在高级复制中进行数据copy的辅助工具。一种是用在数据仓库中用来预处理数据。本文介绍第二种用在数据仓库环境下的物化视图。

物化视图是oracle中一个非常吸引人的功能。当你反复调用一个查询时或者在OLAP系统中有些复杂计算需要提前算出来的时候。就可以创建一个物化视图，这样可以简便查询语句也可以提前准备好数据，提高查询效率。

物化视图可以理解为一张实实在在的表，不过这张表的数据是通过select其他表（我们称为master table）生成的。

创建物化视图需要的相关权限。

1.最基本的你需要create materialized view系统权限同时create table或者create any table系统权限

2.对于master table我们要有select（read）对象权限或者有select any table（read any table）系统权限

3.如果常见refresh on commit materialized view还需要on commit refresh 对象权限或者系统权限

4.创建enable query rewrite 物化视图需要query rewrite对象权限或者global query rewrite 权限

6.因为物化视图是实实在在占用物理存储空间的，所以创建物化视图的用户还需要足够的空间。

7.DB为了维护物化视图，在创建物化视图的时候DB会自动创建一张internal table，最少一个索引还有可能创建一些view，所以这些权限也是要有的。

物化视图种类

1.materialized aggregate view

2.materialized join view

3.primary-key based or rowid-based single table materialized view

4.union all materialized view：以上三种组合union all起来的视图

物化视图创建语法详解：

create materialized view [view_name]:固定语法

of object_type:指定创建的物化视图是对象物化视图。参见http://blog.csdn.net/shengsummer/article/details/44785695

scope_table_ref_constraint:为物化视图中的列添加ref 约束，ref约束参见http://www.cnblogs.com/kerrycode/archive/2012/05/13/2454614.html

column_alias:列别名。如果省略就与select保持一致。encrypt 看着像加密之类的，但是官方没有找到合理解释，在此不做详细说明

on prebuilt table：这个字句可以将一个已经存在的表转换为物化视图。理解起来比较困难。接官方的一个demo

CREATE TABLE sales_sum_table
   (month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2));

CREATE MATERIALIZED VIEW sales_sum_table
   ON PREBUILT TABLE WITH REDUCED PRECISION
   ENABLE QUERY REWRITE
   AS SELECT t.calendar_month_desc AS month, 
             c.cust_state_province AS state,
             SUM(s.amount_sold) AS sales
      FROM times t, customers c, sales s
      WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
      GROUP BY t.calendar_month_desc, c.cust_state_province;

with/without reduced precision:是否允许丢失精度。因为使用on prebuilt table存在表和select 字句查处来的数据精度不一致，所以可以通过该子句指定是否与徐丢失精度，默认不允许丢失精度，也就是如果精度不一致那么就创建失败。

physical_properties：这个字句比较复杂。里面包含很多配置项，主要是关于extents，segments，storage属性的配置，一般默认即可。参见：http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2129582

materialized_view_props:指定物化视图属性，这个选项和on prebuilt table是互斥的，这个选项就是不基于现有表创建一个物化视图

column_properties:配置物化视图列属性，和create table中语法一致

table_partitioning_clause:物化视图也可以像普通表一样分区，语法参http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2215406

using index/no index:using index 会自动创建一个索引以便使用fast refresh。如果使用no index 那么就不会创建默认的index，需要手动显示创建一个index才能使用fast refresh。

create_mv_refresh:是整个物化视图最核心的部分，配置物化视图的刷新方法，模式，时间。

具体参数同materialize log：http://blog.csdn.net/shengsummer/article/details/4477541

补充

on demand：刷新模式下，物化视图将不会自动刷新。默认设置是调用dbms_mview 里面的函数来手动刷新。也可以通过start with...next来覆盖默认设置。而start with...next 优先级高于on demand。所以使用start with...next就没有必要使用on demand。

for update：指定了这个配置，配合高级更新advanced replication使用可以保证对物化视图的更新反写到master table。

evaluation_edition_clause:不知道干啥的，好像和editioned-Object相关。后面补充。

query_rewrite_clause:这个选项在sql调优的时候可以引导优化器根据物化视图重写sql

enable:允许优化器根据物化视图重写sql，查询重写有两个条件，第一，无话视图中的函数都应该是稳定的，也就是每次结果都一样。第二，不允许初选sysdate，sequence.nextVal等变化值。

as subquery：subquery就是物化视图创建和刷新时执行的语句。

subquery有一些限制

1.如果指定了enable query rewrite 就不能select from views，materialized views

2.materialized join/aggregate view 语句中有group by 不能select from索引组织表

3.不能包含long和long raw类型

4.不能包含虚拟列

例子参考oracle：

http://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302