hive物化视图和表的区别物化视图缺点

转载

恋上一只猪 2024-01-08 19:41:31

文章标签 hive物化视图和表的区别 oracle物化视图的局限物化视图数据数据库 文章分类 Hive 大数据

物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。1、物化视图的类型：ON

DEMAND、ON

COMMIT二者的区别在于刷新方法的不同，ON

DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；而ON

COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。2、ON

DEMAND物化视图物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。物化视图的特点：(1)

物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证；(2)

物化视图也是一种段(segment)，所以其有自己的物理存储属性；(3)

物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；创建语句：create

materialized view mv_name as select * from table_name默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。物化视图的数据怎么随着基表而更新？Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。ON

DEMAND物化视图的特性及其和ON

COMMIT物化视图的区别，即前者不刷新(手工或自动)就不更新物化视图，而后者不刷新也会更新物化视图，——只要基表发生了COMMIT。

创建定时刷新的物化视图：create

materialized view mv_name refresh force on demand start with
sysdate
next sysdate+1 (指定物化视图每天刷新一次)

上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间(比如每天晚上10：00定时刷新一次)：

create
materialized view mv_name refresh force on demand start with
sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),'
22:00:00'),'dd-mm-yyyy hh24:mi:ss')

3、ON

COMMIT物化视图ON

COMMIT物化视图的创建，和上面创建ON

DEMAND的物化视图区别不大。因为ON

DEMAND是默认的，所以ON

COMMIT物化视图，需要再增加个参数即可。需要注意的是，无法在定义时仅指定ON

COMMIT，还得附带个参数才行。创建ON

COMMIT物化视图：

create
materialized view mv_name refresh force on commit as select * from

table_name备注：实际创建过程中，基表需要有主键约束，否则会报错(ORA-12014)4、物化视图的刷新

刷新(Refresh)：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：

ON
DEMAND和ON
COMMIT。(如上所述)

刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。

对于已经创建好的物化视图，可以修改其刷新方式，比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次：alter

materialized view mv_name refresh force on demand start with
sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'
22:00:00'),'dd-mm-yyyy hh24:mi:ss')

5、物化视图具有表一样的特征，所以可以像对表一样，我们可以为它创建索引，创建方法和对表一样。6、物化视图的删除：

虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中，并不能用删除表的方式来删除(在表上右键选择‘drop’并不能删除物化视图)，可以使用语句来实现：drop

materialized view mv_name

最近在做一个BI项目，BI项目，就一定会涉及到数据仓库。数据仓库和生产数据库一个很重要的区别就是：生产数据库存放的是当前的实时的数据，而数据仓库存放的是历史数据。这就会引发一个很重要的问题：如何保持生产数据库和数据仓库的同步问题。现实中有多种解决的办法，不过由于刚接触这方面的东西，目前只了解到有2种方法。一种是通过ETL工具创建映射和计划来定期的刷新数据，另一种办法就是物化视图。正在做的这个项目中，这两种方法都用到了(不知道还有没有用到别的方法)。

在项目中，我本身没有用过物化视图，不过在业余的时间里，学习了一下这方面的东西，这里做一个笔记以备以后查询。

刚接触物化视图这个概念的时候，存在以下三个问题：

1、

什么是物化视图？

2、

它有什么用？

3、

怎么操作它？

下面的内容，我将视图回答以上三个问题，重点在于如何操作物化视图。

1、什么是物化视图，以及它有什么用

物化视图是数据库中的一种存储数据的对象，和表一样，存储了数据。用于预先计算并保存表连接或者聚集等耗时较多的操作。这样，在执行查询的时候，就可以避开连接、聚集等耗时的操作，从而快速的得到结果。它可以查询表，视图甚至是其他物化视图中的数据。和视图不同的是，它存储了实实在在的数据，而视图只是存储了定义而已，所以，也是可以在物化视图上创建索引的。物化视图在很多方面，和索引很相似：使用它的主要目的是为了提高查询性能；物化视图对应用是透明的，增加或者删除物化视图不会影响应用程序中查询语句的正确性和有效性；物化视图会占用存储空间；当基表发生变化时，物化视图也应当刷新。通常情况下，物化视图被称为主表(在复制期间)或者明细表(在数据仓库中)。

对于数据仓库这样的数据库来说，由于生产系统中存储了大量的明细和实时的数据，因此，在数据仓库中使用物化视图是非常必要和重要的。

2、操作物化视图

创建物化视图的用户需要一下权限： CREATE MATERIALIZED VIEW, CREATE TABLE, CREATE VIEW,

SELECT，如果需要在其他的模式下创建，需要在表上有CREATE

ANY MATERIALIZED VIEW和SLECT权限。如果是要查询引用别的模式中的物化视图，需要有引用的表的GLOBAL

QRY REWRITE OR QRY REWRITE权限。如果打算使用物化视图，还需要修改或者添加参数文件中QRY_REWRITE_ENABLED选项，值为TR。

2.1、创建物化视图

CREATE MATERIALIZED VIEW MV_NAME [ON PREBUILT TABLE]
[TABLESPACE TABLESPACE_NAME]
[BUILD IMMEDIATE | DEFERRED]
[REFRESH [FAST | COMPLETE | FORCE]
[ON DEMAND |
COMMIT]
[START
WITH DATE]
[NEXT
DATE]
[WITH
PRIMARY KEY | ROWID]]
[ENABLE QRY REWRITE | DISABLE QRY REWRITE]
AS
SELECT CLAUSE ;

以上是创建物化视图的比较完整命令，其中的[

]部分均是可选的。下面分别对它们进行说明。

MV_NAME : 是物化视图的名称一般是XXX_MV形式的；

ON PREBUILT TABLE : 将已经存在的表注册为物化视图。同时还需要提供描述创建该表的查询的SELECT子句。对于这个语句，知道的并不多。

TABLESPACE : 指定物化视图存放的表空间。

BUILD 子句 : 指定何时生成数据。IMMEDIATE指定在创建物化视图时就生成数据；DEFERRED指定在创建时并不生成数据，以后根据需要生成数据。默认为IMMEDIATE选项。

REFRESH 子句 : 指定当基表的数据发生变化时，物化视图何时以何种方式和基表进行同步。FAST指定增量刷新方式，也就是只刷新自上次刷新后被修改的数据；COMPLETE

指定对整个物化视图进行完全刷新；FORCE是默认选项，指定在刷新数据时，先判断是否能够使用FAST方式进行刷新，如果可行，则采用FAST方式刷新，如果不可行，则使用COMPLETE方式刷新。ON

DEMAND指定物化视图在用户需要的时候进行刷新，可以手工刷新，通过使用DBMS_MVIEW.REFRESH等方式刷新，也可以通过JOB方式进行定时刷新，可以到表USER_JOBS中查看JOB的信息；ON

COMMIT指定物化视图在基表的DML操作提交的同时进行刷新。START

WITH指定第一次刷新的时间；NEXT指定刷新的时间间隔。

WITH PRIMARY KEY: 指定生成主键物化视图，也就是物化视图是基于表的主键，而不是ROWID(对应于ROWID选项)。如果要使用WITH

PRIMARYKEY选项，基表上应该定义了主键，否则只能使用ROWID选项。WITH

PRIMARY是默认选项。RowID物化视图只有一个单一的主表，不能包括下面任何一项：Distinct

或者聚合函数；Group

by，子句，连接和Set操作。

ENABLE/DISABLE QRY REWIRTE : 指定是否支持查询重写，默认不支持。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。

SELECT 子句 : 从基表取数据的查询语句，和普通的查询语句没有区别。

这里需要特别注意的是，如果需要进行快速刷新，则需要创建物化视图日志。物化视图日志的详细内容见“物化视图日志”一节。

为了练习，我创建了一张表：

CREATE TABLE tax
(tax_id NUMBER,
region VARCHAR2(30),
tax_date DATE,
tax NUMBER,
PRIMARY KEY (tax_id));

插入了一下数据：

"839","天津","2008-3-1","12038.48"

"840","威海","2008-3-1","15310.32"

"841","温州","2008-3-1","459432.9"

"842","信阳","2008-5-1","1574.73"

"843","徐州","2008-4-1","401.54"

"844","烟台","2008-8-1","17869.17"

"845","盐城","2008-3-1","1156.73"

"846","义乌","2008-3-1","988.22"

下面我将先创建物化视图日志：

CREATE MATERIALIZED VIEW LOG ON tax
TABLESPACE users
WITH ROWID;

然后，我创建物化视图tax_mv：

CREATE MATERIALIZED VIEW tax_mv
TABLESPACE users
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
START WITH trunc(SYSDATE) + 1 NEXT trunc(SYSDATE) + 1
WITH ROWID
DISABLE QRY REWRITE
AS
SELECT
t.tax_id,
t.region,
t.tax_date,
t.tax
FROM
tax t;

以上两条SQL语句，第一条，以ROWID创建表tax的物化视图日志；

第二条，基于ROWID创建物化视图，该物化视图的数据需要马上生成，采用根据用户需要而进行增量刷新的方式同步数据，开始刷新的时间为系统当期时间的第二天，每天刷新一次，并且不支持查询重写。

现在查询物化视图TAX_MV中的数据，和表TAX中的完全一致，因为我们采取了IMMEDIATE的生成数据方式。

2.2、刷新物化视图

现在，我们向表TAX中，插入以下数据:

INSERTINTOtax
VALS(847,'营口',to_date('2008-3-1','yyyy-mm-dd'),219.48);

此时，查询表tax中的数据，自然是多了一条新数据，但是去查询tax_mv中的数据时，并没有新插入的数据，因为数据并没有同步到物化视图中，这得需要下次刷新数据的时候，才能同步过来。不过我们现在可以手工的来同步数据。

执行以下任一程序语句：

SQL Window:
BEGIN
dbms_mview.refresh('tax_mv','C');
END;
COMMAND Window:
EXECUTEdbms_mview.refresh('tax_mv','C');

第一个参数是物化视图名称，第二个参数指明刷新方式，C-COMPLETE,

F-FAST，?-FORCE。

这就手工刷新了物化视图中的数据，去查找物化视图中的数据，已经和基表中的数据同步了。

实际上，oracle提供了两个包：DBMS_MVIEW、DBMS_REFRESH、DBMS_OLAP，用于对物化视图进行管理。这三个包我还没有去好好的学习，大概知道：

DBMS_MVIEW用于管理物化视图；

DBMS_REFRESH用于管理物化视图的刷新；

DBMS_OLAP用于确定物化视图是否能提高查询的性能。

2.3、删除物化视图

删除物化视图很简单，执行如下SQL语句：

DROPMATERIALIZEDVIEWtax_mv;

2.4、管理物化视图

前面我们说过，oracle提供了DBMS_MVIEW、DBMS_REFRESH、DBMS_OLAP三个包来管理物化视图。

DBMS_MVIEW:常用的有以下4个：

Dbms_mview.refresh(list IN VARCHAR2, method IN

VARCHAR2, …)

List :指定需要刷新的物化视图，可以是一张，也可以是以逗号分隔的列表；

Method:指定刷新方式，F-FAST，C-COMPLETE，?-FORCE。不指定刷新方式，会使用它的默认刷新方式，默认刷新方式是存放在数据字典里面的。

Dbms_mivew.refresh_all;执行所有待执行的刷新任务，需要ALTER

ANY SNAPSHOT权限。

Dbms_mview.explain_mview(mv in VARCHAR2,
stmt_id IN VARCHAR2 := null)

用于陈述一个物化视图的能力和性能的。参数mv可以是一个[schema].mvname的视图名称，也可以是一句欲用于创建mv的select语句。结果将会被保存在MV_CAPABILITIES_TABLE中，如果没有这张表，需要预先调用ORACLE_HOME\RDBMS\ADMIN\utlxmu.sql脚本来创建此表。此脚本中，对表中的各字段有较详细的解释，这里不赘述。

针对我前面创建的视图TAX_MV，在COMMAND

WINDOW下面执行了下列语句：

exec dbms_mview.explain_mview('tax_mv');

提示执行完成。但是令我意外的是，表MV_CAPABILITIES_TABLE什么都没有。这个问题留待解决。

Dbms_mview.explain_rewrite(qry in varchar2, mv

in varchar2 :=null, msg_arrag IN OUT

SYS.REWRITEARRAYTYPE);

陈述关于mv的查询重写方面的信息。同explain_mview一样，结果被保存在表REWRITE_TABLE中，通过执行脚本ORACLE_HOME\RDBMS\ADMIN\utlxrw.sql来创建此表。

DBMS_REFRESH

物化视图是可以嵌套的。也就是指一个物化视图可以被放在另一个物化视图的select子句里面。在物化视图相互嵌套的时候，由于各个物化视图的定义不一致，或许由于刷新方式和刷新时间的不同，会引起数据的完整性问题，对于这种情况，可以考虑把管理的物化视图放在同一个刷新组中。DBMS_REFRESH就是专门为刷新组而来的。

USER_REFRESH USER_JOBS
Dbms_refresh.make(name in varchar2, list in
varchar2, next_date in date, interval in
varchar2...)

这是创建一个刷新组，name是刷新组的名称，list是以都好分隔的物化视图名词，next_date是第一次刷新时间，interval是刷新间隔。省略号为更多的默认参数。

BEGIN
dbms_refresh.make('test_refresh_group','tax_mv3',SYSDATE,'sysdate+1');
END;

上语句创建了一个新的刷新组，刷新间隔为1天。

执行这个语句，结果会报错：ORA-23410:实体化视图tax_mv3已在刷新组中，查了些资料，原来是在创建物化视图的时候，会自动创建一个刷新组，名称和物化视图名称一样。这可以从表user_refresh可以查看到结果。解决办法就是，先从默认的刷新组中移除这个物化视图，再执行上面的语句，也就是先执行下面的语句：

BEGIN
dbms_refresh.s
tract(name=>'TAX_MV3',list=>'TAX_MV3');
END;

这就是从刷新组TAX_MV3从移除物化视图TAX_MV3。

Dbms_refresh.add(name in varhcar2, list in
varchar2…)

省略号为更多的默认参数。此语句添加一个可刷新对象到刷新组中。

BEGIN
dbms_refresh.add('test_refresh_group','tax_mv1');
END;
Dbms_refresh.s tract(name in varchar2, list in
varchar2…)

省略号为更多的默认参数。此语句从刷新组中清除指定的物化可刷新对象。

BEGIN
dbms_refresh.s
tract('test_refresh_group','tax_mv3');END;
Dbms_refresh.refresh(name in
varhcar2)

刷新制定的刷新组。

BEGIN
dbms_refresh.refresh('test_refresh_group');
END;
Dbms_refresh.destroy(name in
varchar2)

删除指定的刷新组。

BEGIN
dbms_refresh.destroy('test_refresh_group');
END;

2.5、物化视图的日志

前面已经简单介绍了物化视图日志在某些情况下的必要性。

物化视图日志是一个表，记录了对物化视图操作的历史记录。

物化视图日志的表名被保存在ALL_MVIEW_LOGS这张表中，可以通过主表名来查看它的物化视图日志。如以下语句：

SELECT*
FROMall_mview_logs
aml
WHEREaml.MASTER
='TAX';

查询主表为TAX的物化视图日志。结果中，一个字段叫做LOG_TABLE，对于表TAX来说，物化视图日志表名是MLOG$_TAX。现在就可以去查找表MLOG$_TAX来查看物化视图日志了。

要创建物化视图日志必须有CREATE

TRIGGER，CREATE

TABLE权限.

物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY

KEY类型的。还可以选择是否包括SEQNCE、INCL

ING NEW VALS以及指定列的列表。

创建物化视图日志的语法如下：

CREATEMATERIALIZEDVIEWLOGON[TABLE_NAME]
TABLESPACE[TABLESPACE_NAME]
WITH[OBJECTID|PRIMARYKEY|ROWID|SEQNCE]

TABLE_NAME，TABLESPACE_NAME是指基表名称和物化视图日志存放的表空间。

WITH子句中，OBJECT

ID指对象ID，如果是对象物化视图，则只能采用这个方式；

PRIMARY KEY是根据基表主键创建日志，ROWID是根据ROWID创建日志，SEQNCE则是根据序列号创建日志。

删除物化视图日志的语句是

DROPMATERIAZLIEDVIEWLOGON[TABLE_NAME]；

学习过程中，还知道了SNAPSHOT这个概念，它是一个表，包含了对一个本地或者远程数据库上一个或者多个表或视图的查询结果。由于它是一个主表的查询子集，使用快照统一可以加快数据的查询速度；在保持不同数据库中的两个表的同步中，利用快照刷新，数据的更新性能也会有很大改善。这样的话，就不得不比较一下它和物化视图了，才知道，原来它和物化视图几乎是同一回事，只在底层有一些差异，不过现在不怎么用了，改用MV了，现在还保留snapshot这个说法，只是为了兼容而已。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。