最近一OLTP生产数据库采用物化视图后性能明显提升，越来越发觉物化视图是把利器了。今天继续尝试物化视图中的一些奇妙的地方。如下是案例的整个过程，供大家学习和引用！

[@more@]

1、先创建两张表test_1和test_2：
SQL> conn shiyihai/shiyihai@db80
Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0
Connected as shiyihai

SQL>
SQL> drop table test_1;

Table dropped

SQL> create table test_1
2 (
3 test_date date,
4 id number(8),
5 address varchar2(24)
6 );

Table created

SQL> drop table test_2;

Table dropped

SQL> create table test_2
2 (
3 id number(8),
4 name varchar2(24),
5 flag varchar2(2)
6 );

Table created

SQL>
2、创建单表test_1的物化视图：
SQL> drop materialized view mv_test_1;

drop materialized view mv_test_1

ORA-12003: 实体化视图 "SHIYIHAI"."MV_TEST_1" 不存在

SQL> create materialized view mv_test_1
2 as
3 select * from test_1;

create materialized view mv_test_1
as
select * from test_1

ORA-12014: 表'TEST_1'不包含主键约束条件

SQL>
这里我们可以看到，在单表上创建mv时需源表包含主键约束才行，不然会抛异常ORA-12014。

3、创建两表（test_1和test_2）关联的物化视图：
SQL> drop materialized view mv_test;

Materialized view dropped

SQL> create materialized view mv_test
2 as
3 select a.test_date,a.id,a.address,b.name from test_1 a,test_2 b
4 where a.id=b.id
5 and b.flag='1';

Materialized view created

SQL>
表关联时不需要主键约束，并且默认刷新方式是refresh force on demand。

4、创建分区物化视图：
drop materialized view mv_test_partition;
create materialized view mv_test_partition
partition by range(test_date)
(
PARTITION P_200711 values less than (TO_DATE(' 2007-12', 'YYYY-MM')),
PARTITION P_200712 values less than (TO_DATE(' 2008-01', 'YYYY-MM')),
PARTITION P_200801 values less than (TO_DATE(' 2008-02', 'YYYY-MM')),
PARTITION P_200702 values less than (TO_DATE(' 2008-03', 'YYYY-MM')),
PARTITION p_max values less than (maxvalue)
)
as
select a.test_date as test_date,a.id,a.address,b.name from test_1 a,test_2 b
where a.id=b.id
and b.flag='1';
总结：物化视图实际上是一张表，在表上能做的事情在物化视图上也可以满足。