物化视图创建一例

如何创建最简单的物化视图

[@more@]

在生产库上给原表创建物化视图日志：
create materialized view log on sb_wsz_zsxx with rowid;

在bi数据库上创建物化视图：

CREATE MATERIALIZED VIEW mv_ods_sb_wsz_zsxx
REFRESH fast with rowid
START WITH TO_DATE('09-09-2008 13:18:38', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE+1 as
select * from sb_wsz_zsxx@ZZDS15

如果想试一试刷新可手动执行：
exec dbms_snapshot.refresh('MV_ODS_SB_WSZ_ZSXX');

看看刷新是不是正常。

创建分区物化视图：
CREATE MATERIALIZED VIEW ods_sb_wsz_zsxx PARTITION BY RANGE (rq_rk)
(PARTITION P200301_B VALUES LESS THAN ('2003-01-31'),
PARTITION P200301 VALUES LESS THAN ('2003-02-31'),
PARTITION P200302 VALUES LESS THAN ('2003-03-31'),
PARTITION P200303 VALUES LESS THAN ('2003-04-31'),
PARTITION P200304 VALUES LESS THAN ('2003-05-31'),
PARTITION P200305 VALUES LESS THAN ('2003-06-31'),
PARTITION P200306 VALUES LESS THAN ('2003-07-31'),
PARTITION P200307 VALUES LESS THAN ('2003-08-31'),
PARTITION P200308 VALUES LESS THAN ('2003-09-31'),
PARTITION P200309 VALUES LESS THAN ('2003-10-31'),
PARTITION P200310 VALUES LESS THAN ('2003-11-31'),
PARTITION P200311 VALUES LESS THAN ('2003-12-31'),
PARTITION P200312 VALUES LESS THAN ('2004-01-31'),
PARTITION P200401 VALUES LESS THAN ('2004-02-31'),
PARTITION P200402 VALUES LESS THAN ('2004-03-31'),
PARTITION P200403 VALUES LESS THAN ('2004-04-31'),
PARTITION P200404 VALUES LESS THAN ('2004-05-31'),
PARTITION P200405 VALUES LESS THAN ('2004-06-31'),
PARTITION P200406 VALUES LESS THAN ('2004-07-31'),
PARTITION P200407 VALUES LESS THAN ('2004-08-31'),
PARTITION P200408 VALUES LESS THAN ('2004-09-31'),
PARTITION P200409 VALUES LESS THAN ('2004-10-31'),
PARTITION P200410 VALUES LESS THAN ('2004-11-31'),
PARTITION P200411 VALUES LESS THAN ('2004-12-31'),
PARTITION P200412 VALUES LESS THAN ('2005-01-31'),
PARTITION P200501 VALUES LESS THAN ('2005-02-31'),
PARTITION P200502 VALUES LESS THAN ('2005-03-31'),
PARTITION P200503 VALUES LESS THAN ('2005-04-31'),
PARTITION P200504 VALUES LESS THAN ('2005-05-31'),
PARTITION P200505 VALUES LESS THAN ('2005-06-31'),
PARTITION P200506 VALUES LESS THAN ('2005-07-31'),
PARTITION P200507 VALUES LESS THAN ('2005-08-31'),
PARTITION P200508 VALUES LESS THAN ('2005-09-31'),
PARTITION P200509 VALUES LESS THAN ('2005-10-31'),
PARTITION P200510 VALUES LESS THAN ('2005-11-31'),
PARTITION P200511 VALUES LESS THAN ('2005-12-31'),
PARTITION P200512 VALUES LESS THAN ('2006-01-31'),
PARTITION P200601 VALUES LESS THAN ('2006-02-31'),
PARTITION P200602 VALUES LESS THAN ('2006-03-31'),
PARTITION P200603 VALUES LESS THAN ('2006-04-31'),
PARTITION P200604 VALUES LESS THAN ('2006-05-31'),
PARTITION P200605 VALUES LESS THAN ('2006-06-31'),
PARTITION P200606 VALUES LESS THAN ('2006-07-31'),
PARTITION P200607 VALUES LESS THAN ('2006-08-31'),
PARTITION P200608 VALUES LESS THAN ('2006-09-31'),
PARTITION P200609 VALUES LESS THAN ('2006-10-31'),
PARTITION P200610 VALUES LESS THAN ('2006-11-31'),
PARTITION P200611 VALUES LESS THAN ('2006-12-31'),
PARTITION P200612 VALUES LESS THAN ('2007-01-31'),
PARTITION P200701 VALUES LESS THAN ('2007-02-31'),
PARTITION P200702 VALUES LESS THAN ('2007-03-31'),
PARTITION P200703 VALUES LESS THAN ('2007-04-31'),
PARTITION P200704 VALUES LESS THAN ('2007-05-31'),
PARTITION P200705 VALUES LESS THAN ('2007-06-31'),
PARTITION P200706 VALUES LESS THAN ('2007-07-31'),
PARTITION P200707 VALUES LESS THAN ('2007-08-31'),
PARTITION P200708 VALUES LESS THAN ('2007-09-31'),
PARTITION P200709 VALUES LESS THAN ('2007-10-31'),
PARTITION P200710 VALUES LESS THAN ('2007-11-31'),
PARTITION P200711 VALUES LESS THAN ('2007-12-31'),
PARTITION P200712 VALUES LESS THAN ('2008-01-31'),
PARTITION P200801 VALUES LESS THAN ('2008-02-31'),
PARTITION P200802 VALUES LESS THAN ('2008-03-31'),
PARTITION P200803 VALUES LESS THAN ('2008-04-31'),
PARTITION P200804 VALUES LESS THAN ('2008-05-31'),
PARTITION P200805 VALUES LESS THAN ('2008-06-31'),
PARTITION P200806 VALUES LESS THAN ('2008-07-31'),
PARTITION P200807 VALUES LESS THAN ('2008-08-31'),
PARTITION P200808 VALUES LESS THAN ('2008-09-31'),
PARTITION P200809 VALUES LESS THAN ('2008-10-31'),
PARTITION P200810 VALUES LESS THAN ('2008-11-31'),
PARTITION P200811 VALUES LESS THAN ('2008-12-31'),
PARTITION P200812 VALUES LESS THAN ('2009-01-31'),
PARTITION P200901 VALUES LESS THAN ('2009-02-31'),
PARTITION P200902 VALUES LESS THAN ('2009-03-31'),
PARTITION P200903 VALUES LESS THAN ('2009-04-31'),
PARTITION P200904 VALUES LESS THAN ('2009-05-31'),
PARTITION P200905 VALUES LESS THAN ('2009-06-31'),
PARTITION P200906 VALUES LESS THAN ('2009-07-31'),
PARTITION P200907 VALUES LESS THAN ('2009-08-31'),
PARTITION P200908 VALUES LESS THAN ('2009-09-31'),
PARTITION P200909 VALUES LESS THAN ('2009-10-31'),
PARTITION P200910 VALUES LESS THAN ('2009-11-31'),
PARTITION P200911 VALUES LESS THAN ('2009-12-31'),
PARTITION P200912 VALUES LESS THAN ('2010-01-31'),
PARTITION P201001 VALUES LESS THAN ('2010-02-31'),
PARTITION P201002 VALUES LESS THAN ('2010-03-31'),
PARTITION P201003 VALUES LESS THAN ('2010-04-31'),
PARTITION P201004 VALUES LESS THAN ('2010-05-31'),
PARTITION P201005 VALUES LESS THAN ('2010-06-31'),
PARTITION P201006 VALUES LESS THAN ('2010-07-31'),
PARTITION P201007 VALUES LESS THAN ('2010-08-31'),
PARTITION P201008 VALUES LESS THAN ('2010-09-31'),
PARTITION P201009 VALUES LESS THAN ('2010-10-31'),
PARTITION P201010 VALUES LESS THAN ('2010-11-31'),
PARTITION P201011 VALUES LESS THAN ('2010-12-31'),
PARTITION P201012 VALUES LESS THAN ('2011-01-31'),
PARTITION P201101 VALUES LESS THAN ('2011-02-31'),
PARTITION P201102 VALUES LESS THAN ('2011-03-31'),
PARTITION P201103 VALUES LESS THAN ('2011-04-31'),
PARTITION P201104 VALUES LESS THAN ('2011-05-31'),
PARTITION P201105 VALUES LESS THAN ('2011-06-31'),
PARTITION P201106 VALUES LESS THAN ('2011-07-31'),
PARTITION P201107 VALUES LESS THAN ('2011-08-31'),
PARTITION P201108 VALUES LESS THAN ('2011-09-31'),
PARTITION P201109 VALUES LESS THAN ('2011-10-31'),
PARTITION P201110 VALUES LESS THAN ('2011-11-31'),
PARTITION P201111 VALUES LESS THAN ('2011-12-31'),
PARTITION P201112 VALUES LESS THAN ('2012-01-31'),
PARTITION P201201 VALUES LESS THAN ('2012-02-31'),
PARTITION P201202 VALUES LESS THAN ('2012-03-31'),
PARTITION P201203 VALUES LESS THAN ('2012-04-31'),
PARTITION P201204 VALUES LESS THAN ('2012-05-31'),
PARTITION P201205 VALUES LESS THAN ('2012-06-31'),
PARTITION P201206 VALUES LESS THAN ('2012-07-31'),
PARTITION P201207 VALUES LESS THAN ('2012-08-31'),
PARTITION P201208 VALUES LESS THAN ('2012-09-31'),
PARTITION P201209 VALUES LESS THAN ('2012-10-31'),
PARTITION P201210 VALUES LESS THAN ('2012-11-31'),
PARTITION P201211 VALUES LESS THAN ('2012-12-31'),
PARTITION P201212 VALUES LESS THAN ('2013-01-31'),

PARTITION P_max VALUES LESS THAN (MAXVALUE))
REFRESH fast with rowid
START WITH TO_DATE('09-09-2008 00:18:38','DD-MM-YYYY HH24:MI:SS') NEXT sysdate+1 as
select * from sb_wsz_zsxx@ZZDS15;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18976069/viewspace-1017154/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18976069/viewspace-1017154/