oracle including new values,Oracle物化视图测试例子

Oracle物化视图是一个强大的功能，可以用于

1.预计算：比如一些大计算量的OLAP语句，可用物化视图来提升性能，避免重复计算

2.数据同步：不同库之间同步数据，代替exp/imp，不过估计对少数超级大表有意义

【管理操作】

drop materialized view log on test;  --删除视图日志

drop materialized view mv_test;  --删除视图

exec

dbms_mview.refresh('MV_TEST'); --强制刷新视图：

【创建测试表】

create table stu (id number(3,0) primary key, class number(2,0) not

null,name varchar(20) not null,age number(3));

create table test (id number(3,0),score number(3,1),foreign key

(id) references stu(id));

insert into stu values(1,1,'zhangsan',8);

insert into stu values(2,1,'lisi',9);

insert into stu values(3,2,'wanger',8);

insert into stu values(4,3,'mazi',7);

insert into test values(1,88);

insert into test values(2,67);

insert into test values(3,45);

insert into test values(4,99);

commit;

【---------------------------周期性更新或手动更新的例子----------------】

CREATE MATERIALIZED VIEW

mv_test(avg_score,max_score,min_score)

TABLESPACE

users NOLOGGING

BUILD

DEFERRED

USING INDEX

TABLESPACE users

REFRESH

START WITH ROUND(SYSDATE + 1) + 1/24

NEXT

NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 23/24

disable

QUERY REWRITE

AS SELECT

avg(score),max(score),min(score) from test;

--会生成job自动执行，参见dba_jobs

--build

deferred表示不在建视图时初始化数据，如果是build immediate则立即初始化

CREATE MATERIALIZED VIEW mv_test

TABLESPACE

users LOGGING

BUILD

IMMEDIATE

USING INDEX

TABLESPACE users

REFRESH

COMPLETE ON DEMAND

ENABLE

QUERY REWRITE

AS SELECT

stu.class,sum(test.score),count(test.score),count(*) from stu, test

where stu.id=test.id group by stu.class;

--enable

query

rewrite开启查询重写，即便SQL中没有用到物化视图优化器也可能转化成查询物化视图，建议视图建立后立即收集统计信息。支持重写有个重大意义是可以对应用透明，DBA做优化好用。

--REFESH COMPLETE表示每次刷新都是全部数据更新，如果是refresh

fast则根据物化视图日志增量更新，速度较快。还有refresh

force(默认)，表示自动判断是否可以应用fast，不能则用complete。

【----------------------------------------快速更新的例子---------------】

--创建物化视图日志

CREATE MATERIALIZED VIEW LOG ON stu

TABLESPACE users

WITH ROWID,

SEQUENCE(id,class,age)

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON test

TABLESPACE users

WITH ROWID, SEQUENCE(id,

score)

INCLUDING NEW VALUES;

--会在指定的表空间中创建日志表：MLOG$_TEST,RUPD$_TEST；with

rowid/primarykey会将这些信息记入日志，including new

values则会将update的newvalue记录到日志，估计这样可能会加快日志的应用

--创建物化视图：

CREATE MATERIALIZED VIEW mv_test--(sum_id,max_name)

TABLESPACE

users

BUILD

IMMEDIATE

USING INDEX

TABLESPACE users

REFRESH ON

COMMIT FAST

ENABLE

QUERY REWRITE

AS SELECT

stu.class,sum(test.score),count(test.score),count(*) from stu, test

where stu.id=test.id group by stu.class;

--对FAST刷新来说count(*)是必需的，而且不能有avg/min/max/having，有min/max的话则不支持UPDATE/DELETE，avg/having不支持！

--对JOIN来说必需有对sum项的count,同时还必需有count(*)，否则不支持UPDATE/DELETE!

--快速更新对UPDATE/DELETE比较受限，许多情况下都不支持

【检查物化视图的错误、功能属性】

要求视图已经建立成功才能使用，一般用于查看物化视图有哪些功能，比如是否可以快速刷新、是否支持查询重写，而且会注明不能的原因。

@$ORACLE_HOME/rdbms/admin/utlxmv.sql

--建MV_CAPABILITIES_TABLE表

truncate table MV_CAPABILITIES_TABLE;

exec DBMS_MVIEW.EXPLAIN_MVIEW('MV_TEST');

select * from MV_CAPABILITIES_TABLE;

【查看物化视图的相关视图】

dba_objects

dba_tables

all_mviews

all_mview_refresh_times

all_mview_aggregates

all_mview_analysis

all_mview_comments

all_mview_detail_relations

all_mview_joins

all_mview_keys

all_mview_logs

【参考】

Oracle Database SQL Reference：CREATE MATERIALIZED VIEW LOG AND

VIEW

http://yangtingkun.itpub.net/post/468/16456

http://www.itpub.net/thread-1308625-1-1.html