Oracle-day03 中

111

　　二、物化视图
　　（一）什么是物化视图
　　视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。
　　物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。
　　（二）创建物化视图语法
　　

CREATE METERIALIZED VIEW view_name　　
[BUILD IMMEDIATE | BUILD DEFERRED ]
　　
REFRESH [FAST|COMPLETE|FORCE]
　　
[
　　
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
　　
(next_time)
　　
AS
　　
subquery
　　

　　BUILD IMMEDIATE 是在创建物化视图的时候就生成数据
　　BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。
　　默认为 BUILD IMMEDIATE。
　　刷新（REFRESH）：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。
　　REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE 刷新对整个物化视图进行完全的刷新。如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。FORCE 是默认的方式。
　　刷新的模式有两种：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要
　　手动刷新物化视图（默认）。ON COMMIT 指在基表发生 COMMIT 操作时自动刷新。
　　（三）案例
　　1.创建手动刷新的物化视图

　　需求：查询地址>

　　语句：
　　

create materialized view mv_address　　
as
　　
select ad.id,ad.name adname,ar.name ar_name
　　
from t_address ad,t_area ar
　　
where ad.areaid=ar.id
　　

　　执行上边的语句后查询
　　select * from mv_address;
　　查询结果如下：

　　这时，我们向地址表（T_ADDRESS）中插入一条新记录，
　　insert into t_address values(8,'宏福苑小区',1,1);
　　再次执行上边的语句进行查询，会发现新插入的语句并没有出现在物化视图中。
　　我们需要通过下面的语句（PL/SQL），手动刷新物化视图：
　　

begin　　
DBMS_MVIEW.refresh('MV_ADDRESS','C');
　　
end;
　　

　　或者通过下面的命令手动刷新物化视图：
　　EXEC DBMS_MVIEW.refresh('MV_ADDRESS','C');
　　注意：此语句需要在命令窗口中执行。
　　执行此命令后再次查询物化视图，就可以查询到最新的数据了。
　　DBMS_MVIEW.refresh 实际上是系统内置的存储过程，关于存储过程我们在第 4章会详细讲解。
　　2.创建自动刷新的物化视图，和上例一样的结果集
　　语句如下：
　　

create materialized view mv_address2　　
refresh
　　
on commit
　　
as
　　
select ad.id,ad.name adname,ar.name ar_name
　　
from t_address ad,t_area ar
　　
where ad.areaid=ar.id
　　

　　创建此物化视图后，当 T_ADDRESS 表发生变化时，MV_ADDRESS2 自动跟着改变。
　　3.创建时不生成数据的物化视图
　　

　　
create materialized view mv_address3
　　
build deferred
　　
refresh
　　
on commit
　　
as
　　
select ad.id,ad.name adname,ar.name ar_name
　　
from t_address ad,t_area ar
　　
where ad.areaid=ar.id;
　　

　　创建后执行下列语句查询物化视图
　　select * from mv_address3
　　查询结果：

　　执行下列语句生成数据
　　

begin　　
DBMS_MVIEW.refresh('MV_ADDRESS3','C');
　　
end;
　　

　　再次查询，得到结果：

　　由于我们创建时指定的 on commit ,所以在修改数据后能立刻看到最新数据，无须再次执行 refresh
　　4.创建增量刷新的物化视图
　　如果创建增量刷新的物化视图，必须首先创建物化视图日志
　　

create materialized view log on t_address with rowid；　　
create materialized view log on t_area with rowid
　　

　　创建的物化视图日志名称为 MLOG$_表名称
　　创建物化视图
　　

create materialized view mv_address4　　
refresh fast
　　
as
　　
select ad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.name
　　
adname,ar.name ar_name
　　
from t_address ad,t_area ar
　　
where ad.areaid=ar.id;
　　

　　注意：创建增量刷新的物化视图，必须：

• 创建物化视图中涉及表的物化视图日志。
  
• 在查询语句中，必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志 )　　当我们向地址表插入数据后，物化视图日志的内容：
  
  
  
  　　SNAPTIME$$：用于表示刷新时间。
  　　DMLTYPE$$：用于表示 DML 操作类型，I 表示 INSERT，D 表示 DELETE，U表示 UPDATE。
  　　OLD_NEW$$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U 表示 UPDATE 操作。
  　　CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。
  　　此列是 RAW 类型，其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。插入操作显示为：FE, 删除显示为：OO 更新操作则根据更新字段的位置而显示
  　　不同的值。
  　　当我们手动刷新物化视图后，物化视图日志被清空，物化视图更新。
  
  

　　

begin　　
DBMS_MVIEW.refresh('MV_ADDRESS4','C');
　　
end;
　　

　　三、序列
　　（一）什么是序列
　　序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象。
　　（二）创建与使用简单序列
　　创建序列语法：
　　create sequence 序列名称
　　通过序列的伪列来访问序列的值
　　NEXTVAL 返回序列的下一个值
　　CURRVAL 返回序列的当前值
　　注意：我们在刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次提取当前值。
　　提取下一个值
　　select 序列名称.nextval from dual
　　提取当前值
　　select 序列名称.currval from dual
　　（三）创建复杂序列
　　语法：
　　

CREATESEQUENCEsequence //创建序列名称　　
[INCREMENT BYn]//递增的序列值是 n如果 n是正数就递增,如果是负数就递减 默
　　
认是 1
　　
[STARTWITHn]//开始的值,递增默认是 minvalue递减是 maxvalue
　　
[{MAXVALUEn |NOMAXVALUE}]//最大值
　　
[{MINVALUEn |NOMINVALUE}]//最小值
　　
[{CYCLE| NOCYCLE}]//循环/不循环
　　
[{CACHEn |NOCACHE}];//分配并存入到内存中
　　

　　（四）案例
　　1.有最大值的非循环序列
　　创建序列的语句：
　　

create sequence seq_test1　　
increment by 10
　　
start with 10
　　
maxvalue 300
　　
minvalue 20
　　

　　以上的错误，是由于我们的开始值小于最小值 。开始值不能小于最小值，修改
　　以上语句：
　　

create sequence seq_test1　　
increment by 10
　　
start with 10
　　
maxvalue 300
　　
minvalue 5
　　

　　我们执行下列语句提取序列值，当序列值为 300（最大值）的时候再次提取值，
　　系统会报异常信息。

　　2.有最大值的循环序列
　　

create sequence seq_test2　　
increment by 10
　　
start with 10
　　
maxvalue 300
　　

　　
minvalue 5
　　
cycle ;
　　

　　当序列当前值为 300（最大值），再次提取序列的值
　　select seq_test2.nextval from dual
　　提取的值为：

　　由此我们得出结论，循环的序列，第一次循环是从开始值开始循环，而第二次循
　　环是从最小值开始循环。
　　思考问题：
　　下列语句是否会报错？为什么？
　　

create sequence seq_test3　　
increment by 10
　　
start with 10
　　
minvalue 5
　　
cycle ;
　　

　　答：此为错误的语句。因为你创建的是一个循环的序列，所以必须指定最大值，
　　否则会报错。
　　3.带缓存的序列
　　我们执行下列语句：
　　

create sequence seq_test3　　
increment by 10
　　
start with 10
　　
maxvalue 300
　　
minvalue 5
　　
cycle
　　
cache 50;
　　

　　我们执行上边语句的意思是每次取出 50 个缓存值，但是执行会提示错误

　　上边错误提示的意思是：缓存设置的数必须小于每次循环的数。
　　我们缓存设定的值是 50，而最大值是 300，那么为什么还会提示这样的信息呢？
　　其实我们的 cache 虽然是 50，但是我们每次增长值是 10。这样 50 次缓存提取出
　　的数是 500 （50*10）
　　我们更改为下列的语句：
　　

create sequence seq_test4　　
increment by 10
　　
start with 10
　　
maxvalue 500
　　
minvalue 10
　　
cycle
　　
cache 50;
　　

　　下列语句依然会提示上边的错误，这是因为还存在一个 minvalue ，minvalue 和
　　maxvalue 之间是 490 个数，也就是一次循环可以提取 490，但是我们的缓存是
　　500。
　　我们再次修改语句：
　　

　　
create sequence seq_test5
　　
increment by 10
　　
start with 10
　　
maxvalue 500
　　
minvalue 9
　　

　　
cycle
　　
cache 50;
　　

　　把最小值减 1，或把最大值加 1，都可以通过。
　　（五）修改和删除序列

　　修改序列：使用>　　WITH 参数
　　ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;
　　删除序列：
　　DROP SEQUENCE 序列名称;