mysql物化视图_（转）Oracle物化视图

如如何建立在特定的表空间上，这些在其他的物化视图上面几乎都没有任何介绍的。主要以我做的一个例子来操作，如果对物化视图的基本概念清楚了就比较明白在那里写特定的表空间存储了。

1、简单试验在master

site上创建表和mview log

SQL> create table stu (id varchar2(10) primary key ,name varchar2(20));

Table created.

SQL> create materialized view log on stu;

Materialized view log created.

在mv site上创建mview

SQL> create materialized view stu_mv refresh fast start with sysdate next sysdate+1/1440 with primary key as select * from stu@to_vm9;

Materialized view created.

SQL> select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;

JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT

--- --------- ----------- --------- ----------- ---------- -------------- -----------------------------------------

21 SEAGULL 2008-2-18 1 14:41:43 2008-2-18 1 14:42:43 sysdate+1/1440 dbms_refresh.refresh('"SEAGULL"."STU_MV"');

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

STU_MV TABLE

在master site上对master

table做更新：

SQL> INSERT INTO STU(ID,NAME) VALUES('56','555555555555');

1 row created.

SQL> commit;

等1分钟后在mv

site上检查

SQL> select * from stu_mv;

ID NAME

---------- --------------------

56 555555555555

2、跨版本数据迁移利用prebuilt mv实现跨平台，跨版本数据迁移。该方法的实现原理是对于要迁移的表对象，需要有一个主键，用于mv的刷新，对于符合该要求的表，在源表上创建mv日志，再在目标数据库上创建结构一样的表，然后在目标表上采用prebuilt方式创建mv，第一次采用完全刷新，之后采用增量刷新，等真正要切换的时候，只需要刷新完增量的日志，删除mv，保留目标表即可。基本思路的例子：

在源库上创建表和mview log

SQL> create table big_t1 as select * from dba_objects;

Table created.

SQL> select count(1) from big_t1;

COUNT(1)

----------

6170

SQL> create materialized view log on big_t1;

Materialized view log created.

在目标数据库上创建与该表一样的表，并在该表上创建prebuilt mv:

SQL> create table big_t1 as select * from big_t1@to_vm9 where 1=2;

Table created.

SQL> select count(1) from big_t1;

COUNT(1)

----------

0

SQL> create materialized view big_t1 on prebuilt table refresh fast as select * from big_t1@to_vm9;

Materialized view created.

做完全刷新和增量刷新

SQL> exec dbms_mview.refresh('BIG_T1','Complete');

PL/SQL procedure successfully completed.

SQL> select count(1) from big_t1;

COUNT(1)

----------

6170

此时模拟在做完全刷新过程中，源库的表又发生了变化

SQL> insert into big_t1(object_id,owner) values(99991,'test');

1 row created.

SQL> commit;

Commit complete.

再做增量刷新

SQL> select count(1) from big_t1;

COUNT(1)

----------

6170

SQL> exec dbms_mview.refresh('BIG_T1');

PL/SQL procedure successfully completed.

SQL> select count(1) from big_t1;

COUNT(1)

----------

6171

停机切换,做最后一次刷新，然后删除源库的mview

log和目标库的mview：

SQL> exec dbms_mview.refresh('BIG_T1');

PL/SQL procedure successfully completed.

SQL> drop materialized view big_t1;

Materialized view dropped.

SQL> select count(1) from big_t1;

COUNT(1)

----------

6171

这里删除的mview(big_t1)是prebuilt mv，所以删除该mview,并不删除相应的表。如果删除了mvnew(stu_mv),由于是普通mv,则删除了该mview,就没有对应的表了。

SQL> drop materialized view stu_mv;

Materialized view dropped.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

BIG_T1 TABLE

3、创建存储的日志空间SQL> CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv

tablespace ZGMV_DATA --日志保存在特定的表空间

WITH ROWID ;

SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata

tablespace ZGMV_DATA --日志保存在特定的表空间

WITH ROWID,sequence(LEVYDETAILDATAID);

SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata

tablespace ZGMV_DATA --日志保存在特定的表空间

WITH rowid,sequence(LEVYDATAID);

4、然后创建物化视图SQL> create materialized view MV_LVY_LEVYDETAILDATA

TABLESPACE ZGMV_DATA --保存表空间

BUILD DEFERRED --延迟刷新不立即刷新

refresh force --如果可以快速刷新则进行快速刷新，否则完全刷新

on demand --按照指定方式刷新

start with to_date('24-11-2005 18:00:10', 'dd-mm-yyyy hh24:mi:ss') --第一次刷新时间

next TRUNC(SYSDATE+1)+18/24 --刷新时间间隔

as

SELECT levydetaildataid, detaildatano, taxtermbegin, taxtermend,

......

ROUND(taxdeduct * taxpercent1, 2) - ROUND(taxdeduct * taxpercent2, 2) -

ROUND(taxdeduct * taxpercent3, 2) - ROUND(taxdeduct * taxpercent4, 2) -

ROUND(taxdeduct * taxpercent5, 2) taxdeduct, ROUND(taxfinal * taxpercent1, 2) -

ROUND(taxfinal * taxpercent2, 2) - ROUND(taxfinal * taxpercent3, 2) -

ROUND(taxfinal * taxpercent4, 2) - ROUND(taxfinal * taxpercent5, 2) taxfinal,

a.levydataid, a.budgetitemcode, taxtypecode,

......

FROM tb_lvy_levydetaildata a, tb_lvy_levydata c, MV_LVY_LEVYTAXBGTDIV b

WHERE a.levydataid = c.levydataid

AND a.budgetdistrscalecode = b.budgetdistrscalecode

AND a.budgetitemcode = b.budgetitemcode

AND c.incomeresidecode = b.rcvfisccode

AND C.TAXSTATUSCODE='08'

AND C.NEGATIVEFLAG!='9'

5、删除物化视图日志物化视图日志经常会由于物化视图长时间没有刷新，或者基表的一次批量数据更改而变得很大，这会影响物化视图的刷新性能，因此对于这种情况需要对物化视图日志进行处理，降低物化视图日志表的高水位线。

物化视图日志会记录下基表所有的增、删、改操作，而物化视图执行完快速刷新操作后，会从物化视图日志中将本物化视图刷新过且其他物化视图所不需要刷新的记录删除掉。如果其中一个物化视图一直不刷新，那么物化视图日志就会变得越来越大。

还有一种情况，比如表中插入了大量的数据，或者删除了大量的数据，或者将表中的某一列统一更新为一个值，这种操作都会在物化视图日志中产生大量的记录。

而物化视图日志的增大必然影响物化视图的刷新速度。一方面，物化视图在刷新的时候要扫描物化视图日志，另一方面，物化视图在刷新介绍后，也要清除物化视图日志中的记录，仍然要扫描物化视图日志，因此物化视图日志的大小直接会影响物化视图快速刷新的速度。更重要的是，物化视图日志的高水位一旦增长到一个很高的位置，即使以后物化视图日志中记录很少，甚至没有记录存在，物化视图在刷新的时候仍然需要较长的时间。

SQL> DROP materialized view log on mv_lvy_levytaxbgtdiv;

SQL> DROP materialized view log on tb_lvy_levydetaildata;

SQL> DROP materialized view log on tb_lvy_levydata;

6、删除物化视图SQL> drop materialized view MV_LVY_LEVYDETAILDATA;

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