创建物化视图

/*
下面是关于创建物化视图的小例子，首先建了两个表（数据自已填充吧），
接下来就是创建两个物化视图．
*/
-- Create table
create table TEST_M1
(
  ID   VARCHAR2(50) not null,
  NAME VARCHAR2(50)
)
tablespace GS12315_TBS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table TEST_M1
  is '物化视图测试';
-- Create/Recreate primary, unique and foreign key constraints
alter table TEST_M1
  add constraint PK_TEST_M1 primary key (ID)
  using index
  tablespace GS12315_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
------------- 
-- Create table
create table TEST_M2
(
  ID     VARCHAR2(50) not null,
  ADRESS VARCHAR2(50)
)
tablespace GS12315_TBS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table TEST_M2
  is '物化视图测试';
-- Create/Recreate primary, unique and foreign key constraints
alter table TEST_M2
  add constraint PK_TEST_M2 primary key (ID)
  using index
  tablespace GS12315_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

--创建物化视图表日志      
Create materialized view log on  test_m1 WITH rowid;----test_m1为表名
Create materialized view log on  test_m2 WITH rowid;----test_m2为表名 

--删除日志
drop materialized view log on test_m1;  
drop materialized view log on test_m2;  

--创建物化视图语句（在基本提交的时候刷新物化视图）：
Create materialized view MV_TEST_COMMIT
Build immediate
Refresh fast
On commit
With rowid
as
Select m1.id,m1.name,m2.ADRESS,m1.rowid m1rowid,m2.rowid m2rowid
from test_m1 m1,test_m2 m2
where m1.id = m2.id;  
--删除物化视图
drop materialized view MV_TEST_COMMIT;
--查询物化视图
select * from MV_TEST_COMMIT
--创建物化视图语句(每隔一分钟物化视图更新一次数据)：
Create materialized view MV_TEST_DEMAND
Build immediate
REFRESH FAST ON demand
START WITH sysdate+1/(24*60) NEXT sysdate+1/(24*60)
WITH rowid
DISABLE QUERY REWRITE
as
Select m1.id,m1.name,m2.ADRESS,m1.rowid m1rowid,m2.rowid m2rowid
from test_m1 m1,test_m2 m2
where m1.id = m2.id;  
--删除物化视图
drop materialized view MV_TEST_DEMAND;   
--查询物化视图
select * from　MV_TEST_DEMAND