oracle物化视图


ORACLE 实体视图 作者:Q 强 联系方式:xjzhang101@126.com MSN:xjzhang101@126.com 实体视图 是一个数据库对象,它 可以从一个表 戒者多个表中查询出所需要的数据 并且将这些数据存储 在表空间上。 不 一般的视图 丌同的是 实体视图创建完成后,ORACLE 系统就 为这个视图准备好了结果数据,那么 我 们再次查询 该视图的时候,就是 直接得到结果 而丌是 再去查询 这个表戒者多个表的数据,这样 就大 大减少了系统资源的消耗。 那么 当数据源发生变化的时候(也就是 视图中的 表的 内容 发生变化的 时候)实体视图 再重新刷新就 可以了 这样 我们就 可以理解 实体 视图是为了 提高查询的性能。因为 实体 视图他存放的是 查询的结果集, 这些结果集是存放在 表空间中的,所以 实体视图是占用存储空间的。 为了 方便 我们 理解 我们 首先 看一下 一般的视图的概念 视图是查询一个戒者多个表的 SELECT 语句的 描述。 我们 来简单的创建一个视图。 首先 我们需要 创建表,然后写一个 SELECT 语句 。 SQL> create table xjzhang_table1 (a varchar2(10),b number(10)); 表已创建。 SQL> create table xjzhang_table2 (a varchar2(10),b number(10)); 表已创建。 SQL> 我们创建了 两张表 xjzhang_table1 和 xjzhang_table2 下面 我们 向两张表中 揑入数 据 SQL> insert into xjzhang_table1 values ('aaa','00001'); 已创建 1 行。 SQL> insert into xjzhang_table1 values ('bbb','00002'); 已创建 1 行。 SQL> insert into xjzhang_table2 values ('aa1','00002'); 已创建 1 行。 SQL> insert into xjzhang_table2 values ('bb1','00003'); 已创建 1 行。 SQL> commit; 提交完成。 我们分别往 两张表中 揑入了 两行数据 然后 我们写一个 查询语句 SQL> select xjzhang_table1.a,xjzhang_table2.b from xjzhang_table1,xjzhang_table2 where xjzhang_table1.b=xjzhang_table2.b; A B ---------- ---------- bbb 2 然后 我们创建一个试图 视图的名称为 xjzhang_view SQL> create view xjzhang_view as select xjzhang_table1.a,xjzhang_table2.b from xjzhang_table1,xjzhang_table2 where xjzhang_table1.b=xjzhang_table2.b; 视图已创建。 然后 我们查询视图 SQL> select * from xjzhang_view; A B ---------- ---------- bbb 2 可以看到 我们查询 视图的 结果 和查询 那个 SELECT 语句的 结果是一致的 说明 视图是查询 一个 戒者多个表的 SELECT 语句的描述,相信这句话 大家都能理解了。 我们 查询 一下 我们创建的视图 SQL> col object_name for a15 SQL> select object_name,object_type,created,status from dba_objects where object_name='XJZHANG_VIEW'; OBJECT_NAME OBJECT_TYPE CREATED STATUS --------------- ------------------- -------------- ------- XJZHANG_VIEW VIEW 24-6 月 -09 VALID 我们通过 dba_objects 视图 (系统视图),查询出 我们创建视图的信息 OBJECT_NAME 创建视图的 名称:XJZHANG_VIEW OBJECT_TYPE :创建的类型 :VIEW 是视图 CREATED 创建的时间 STATUS 状态 VALID 是有效的 如果视图无效的话 状态为 INVALID 我们 可以 通过 ORACLE 提供的 包 DBMS_METADATA.GET_DDL 来查询出 创建视图的脚本 SQL> select DBMS_METADATA.GET_DDL('VIEW','XJZHANG_VIEW') FROM DUAL; DBMS_METADATA.GET_DDL('VIEW','XJZHANG_VIEW') -------------------------------------------------------------------------- CREATE OR REPLACE FORCE VIEW "SYS"."XJZHANG_VIEW" ("A", "B") AS select xjz 那么视图 我想 我们应该理解了 视图 方便程序员的开发 但是 视图的应用的局限性很小 ,实际上 ORACLE 在做视图 查询的 时候 也就是 转换为 SQL 语句的查询 这样 并没有整体性能的提高。 我们 再来 理解 实体视图 同样 我们 先创建 一张表 表名为 xjzhang_table3 同时 对表 揑入数据 SQL> create table xjzhang_table3 (a varchar2(10),b number(5)); 表已创建。 SQL> insert into xjzhang_table3 values ('aaa','00001'); 已创建 1 行。 SQL> insert into xjzhang_table3 values ('bbb','00002'); 已创建 1 行。 SQL> commit; 提交完成。 我们创建 了一张表,然后插入数据,并且提交。 我们做一个简单的查询如下: SQL> select * from xjzhang_table3; A B ---------- ---------- aaa 1 bbb 2 下面 我们 开始创建 实体视图 (这里 我们创建的实体视图 不是自动刷新 而是需要手动 去刷新) SQL> create materialized view xjzhang_mat_view as select * from xjzhang_table3; 实体化视图已创建。 我们查询一下 我们创建的 实体视图 实体视图的名称为 xjzhang_mat_view SQL> select * from xjzhang_mat_view; A B ---------- ---------- aaa 1 bbb 2 实体视图 从某种意义上说 是一张物理 表 可以通过 DBA_TABLES 进行查询 我们 来 论证一下 SQL> select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='XJZHANG_MAT_VIEW'; TABLE_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ -------- XJZHANG_MAT_VIEW SYSTEM VALID 从 DBA_TABLES 视图中 我们可以看出 实体视图 可以通过 TABLE_NAME 字段 这个条件 来选择 同 时 实体视图 有自己对应的表空间,由于 我们 创建实体视图的时候 没有指定表空间,所以 这里查询出 的结果 是系统表空间 (注意 在生产环境下 丌能放入系统表空间) 我们 来查询一下 刚才创建的 视图 xjzhang_view SQL> select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='XJZHANG_VIEW'; 未选定行 可以看出 普通视图 在 DBA_TABLES 中 是没有记录的,也没有对应的表空间 实体 视图 会占用 一定的 存储空间 因为 它 存放了查询的结果集 那么 它也是一种段, 可以再 DBA_SEGMENTS 中查询出 我们来做个测试 SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name='XJZHANG_MAT_VIEW'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME -------------------- ------------------ ------------------------------ XJZHANG_MAT_VIEW TABLE SYSTEM 同样 我们 通过 DBA_SEGMENTS 来查询一下 我们创建的普通视图 SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name='XJZHANG_VIEW'; 未选定行 可以看出 普通视图 是不被记录在 DBA_SEGMENTS 中的 。 下面 我们更新一下 XJZHANG_TABLE3 表 中的 信息 看一下 实体视图的变化信息 SQL> insert into xjzhang_table3 values ('ccc','00003'); 已创建 1 行。 SQL> commit; 提交完成。 查询 该表的 信息 SQL> select * from xjzhang_table3; A B ---------- ---------- aaa 1 bbb 2 ccc 3 表的 记录增加了 一行 我们再来查询 实体视图的信息 SQL> select * from xjzhang_mat_view; A B ---------- ---------- aaa 1 bbb 2 可以看出 实体视图的信息 没有发生变化 因为 我们在创建 实体视图的 时候 我们没有指定该视图的刷新方法和刷新模式,所以创建 完 该实体视图,该视图默认的刷新方法和刷新模式为 FORCE 和 DEMAND 我们可以通过 dba_mviews 这个 视图 查询 我们创建的 实体视图的信息 SQL> select a.mview_name,a.refresh_mode,a.refresh_method from dba_mviews a where a.mview_name='XJZHANG_MAT_VIEW'; MVIEW_NAME REFRES REFRESH_ ------------------------------ ------ -------- XJZHANG_MAT_VIEW DEMAND FORCE 这里 默认的 是 手工刷新 所以 在这里 我们对实体视图进行更新 SQL> EXEC DBMS_MVIEW.REFRESH('XJZHANG_MAT_VIEW') PL/SQL 过程已成功完成。 然后 我们再次 查询 该实体视图 SQL> SELECT * FROM XJZHANG_MAT_VIEW; A B ---------- ---------- aaa 1 bbb 2 ccc 3 这 说明了 基表的 数据 发生变化,那么 实体视图的 内容 也将 被写入到 对应的存储空 间中。 我们 也可以 创建 自动更新的 实体视图 同样 我们创建一张表 SQL> create table xjzhang_table4 (a varchar2(10),b number(5)); 表已创建。 SQL> insert into xjzhang_table4 values ('aaa','00001'); 已创建 1 行。 SQL> commit; 提交完成。 然后 我们 创建 实体视图 SQL> create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4; create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4 * 第 1 行出现错误: ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性 在创建 时候 报错 在网上 查找资料 下面 我们 一步 一步的 来分析一下 Oracle 提供的 DBMS_MVIEW.EXPLAIN_MVIEW 过程可以帮助你快速定位问题的原因 我们 来 试 一下 ORACLE 提供的包 首先 使用 EXPLAIN_MVIEW 过程首先要建立 MV_CAPABILITIES_TABLE 表 创建步骤 如下 我们执行 一个脚本 来完成创建 SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxmv.sql 表已创建。 然后我们执行 这个包 SQL> begin 2 dbms_mview.explain_mview('select * from xjzhang_table4'); 3 end; 4 / PL/SQL 过程已成功完成。 然后 我们通过 select capability_name, possible, msgtxt from mv_capabilities_table where capability_name like 'REFRESH%' 这个脚本来查询 结果 SQL> col msgtxt for a30 SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME P MSGTXT ------------------------------ - ------------------------------ REFRESH_COMPLETE N 主表中没有任何主键约束条件 REFRESH_FAST N REFRESH_FAST_AFTER_INSERT N 详细信息表没有实体化视图日志 REFRESH_FAST_AFTER_ONETAB_DML N 查看禁用 REFRESH_FAST_AFTER_IN SERT 的原因 REFRESH_FAST_AFTER_ANY_DML N 查看禁用 REFRESH_FAST_AFTER_ON ETAB_DML 的原因 REFRESH_FAST_PCT N PCT 不可能在实体化视图中的任何 从表上 已选择 6 行。 我们 可以看到 第一条 REFRESH_COMPLETE N 主表中没有任何主键约束条件 我们给 xjzhang_table4 创建主键 SQL> alter table xjzhang_table4 add (constraint xjzhang_pri primary key (b)); 表已更改。 然后 我们再次 创建 实体视图 SQL> create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4; 实体化视图已创建。 可以看出 已经成功创建 我们现在向 表中插入 数据 来查看 实体视图的变化情况 我们首先查询一下表中的记录 和实体视图中的记录 SQL> select * from xjzhang_table4; A B ---------- ---------- aaa 1 SQL> select * from xjzhang_mat_view1; A B ---------- ---------- aaa 1 然后 我们向表中插入 一条记录 SQL> insert into xjzhang_table4 values ('afd','00002'); 已创建 1 行。 SQL> commit; 提交完成。 我们 再来查询一下 实体视图的内容 SQL> select * from xjzhang_mat_view1; A B ---------- ---------- aaa 1 物化 视图 为什么 没有变化 我们在网上 查询了 下面的 一段话 刷新(Refresh): 指当基表发生了 DML 操作后,物化视图何时采用哪种方式和基表进行同 步。刷新的模式有两种:ON DEMAND 和 ON COMMIT。ON DEMAND 指物化视图在用户 需要的时候进行刷新,可以手工通过 DBMS_MVIEW.REFRESH 等方法来进行刷新,也可以 通过 JOB 定时进行刷新。ON COMMIT 指出物化视图在对基表的 DML 操作提交的同时进 行刷新。刷新的方法有四种:FAST、COMPLETE、FORCE 和 NEVE*。FAST 刷新采用增 量刷新,只刷新自上次刷新以后进行的修改。COMPLETE 刷新对整个物化视图进行完全的 刷新。如果选择 FORCE 方式,则 Oracle 在刷新时会去判断是否可以进行快速刷新,如果可 以则采用 FAST 方式,否则采用 COMPLETE 的方式。NEVER 指物化视图不进行任何刷新。 默认值是 FORCE ON DEMAND 通过上面的 这段话 我们 知道 刷新的类型 一般有两种:ON DEMAND 和 ON COMMIT ON DEMAND 指物化视图在用户需要的时候进行刷新,可以手工通过 DBMS_MVIEW.REFRESH 等方法来进行刷新 这种方法 也就是 我们长说的 使用 手工刷新 而 ON COMMIT 也就是 我们长说的 自动刷新 而 刷新的 方法 有四种 FAST、COMPLETE、FORCE 和 NEVE* 我们选择的 是 FORCE 说明 ORACLE 是 有选择性的 刷新 如果可以 采用 FAST 要不 才 用 COMPLETE 我们 采用 手工刷新 EXECUTE DBMS_MVIEW.REFRESH('xjzhang_mat_view1','C'); C 代表 完全刷新 F 代表快速刷新和强制刷新 SQL> select * from xjzhang_mat_view1; A B ---------- ---------- aaa 1 SQL> EXECUTE DBMS_MVIEW.REFRESH('xjzhang_mat_view1','C'); PL/SQL 过程已成功完成。 SQL> select * from xjzhang_mat_view1; A B ---------- ---------- aaa 1 afd 2 我们 删除 该实体视图 SQL> drop materialized view xjzhang_mat_view1; 实体化视图已删除。 然后 我们 重新创建 实体 视图 采用 FAST 方法 SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4 * 第 1 行出现错误: ORA-23413: 表 "SYS"."XJZHANG_TABLE4" 不带实体化视图日志 错误提示 需要 带 实体化视图日志 我们 来创建 实体化 视图日志 SQL> create materialized view log on xjzhang_table4 with rowid, sequence (a, b) including new values; 实体化视图日志已创建。 然后 我们再次创建 实体视图 FAST 方法 SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4 * 第 1 行出现错误: ORA-23415: "SYS"."XJZHANG_TABLE4" 的实体化视图日志不记录主键 又提示错误 根据提示错误 我们 使主键 失效 SQL> alter table xjzhang_table4 modify constraint xjzhang_pri disable; 表已更改。 然后 我们 再次 创建 物化视图 FAST SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4 * 第 1 行出现错误: ORA-12014: 表 'XJZHANG_TABLE4' 不包含主键约束条件 这次 系统 又提示 不包含主键约束条件 我们 删除 实体视图 对应的 日志 SQL> DROP MATERIALIZED VIEW LOG ON xjzhang_table4; 实体化视图日志已删除。 我们 在 创建 实体视图日志 的 时候 设定 主键 SQL> create MATERIALIZED VIEW LOG ON xjzhang_table4 WITH PRIMARY KEY; 实体化视图日志已创建。 然后 我们 再次 创建 实体视图 SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; 实体化视图已创建。 可以 看出 创建成功 如果 需要自动 更新 的话 我们 需要 创建一个 自动执行的 JOB (实体视图)物化视图的创建 和基本使用 我已经 基本理解了 在网上 看了 一片文章 叫 ‘Oracle 如何根据物化视图日志快速刷新物化视图’ 根据 他的 帖子 下面 我也 做个试验 首先创建 一张表 SQL> create table wuhua2 (a varchar2(10),b number(5)); 表已创建。 然后 创建 实体物化视图日志 SQL> create materialized view log on wuhua2 with rowid, sequence (a, b) including new values; 实体化视图日志已创建。 我们看一下 mlog$_wuhua1 中的 字段 (实体物化日志中的 列) SQL> desc mlog$_wuhua1; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- A VARCHAR2(10) B NUMBER(5) M_ROW$$ VARCHAR2(255) SEQUENCE$$ NUMBER SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255) 我们 可以看出 A 和 B 就是 我们 刚才创建的表的列,也就是 基表的列。 M_ROW$$ 记录基表中 ROWID 的信息,那么 我们 可以根据 M_ROW$$ 可以定位到 每次 DML 的 操作记录的 信息 SEQUENCE$$ 是根据 DML 操作的 顺序 记录序列的编号,当刷新 时候,SEQUENE$$ 就可以和基表 中的 执行顺序 保持一致 SNAPTIME$ 记录了刷新操作的时间 DMLTYPE 记录了 DML 操作的 类型 是(INSERT UPDATE DELETE) OLD_NEW$$ 物化视图日志中 保存的信息 是 DML 操作之前的值(旧值) 还是 DML 操作之后的值 (新值) CHANGE_VECTOR$$ 表示 DML 操作 发生在 哪几个 字段上 首先 我们插叙一下 实体物化日志中的信息 SQL> select * from mlog$_wuhua2; 未选定行 可以看出 还没有信息 下面 我们 创建一个 物化视图 SQL> create materialized view ceshi_wuhua1 as select * from wuhua1; 实体化视图已创建。 然后 我们修改基表中的信息 SQL> insert into wuhua2 values ('aaaa','00002'); 已创建 1 行。 SQL> insert into wuhua2 values ('cccc','00003'); 已创建 1 行。 SQL> commit; 提交完成。 SQL> commit; 提交完成。 我们 进行了 一系列的 DML 操作 然后 我们查询 实体物化日志信息 SQL> select * from mlog$_wuhua2; 未选定行 然后 手动刷新物化视图 SQL> EXEC DBMS_MVIEW.REFRESH('CESHI_WUHUA2'); PL/SQL 过程已成功完成。 我们再次 查询 实体物化日志信息 SQL> select * from mlog$_wuhua2; A B M_ROW$$ SEQUENCE$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ----- ------ -------------------- ---------- ----------- --------- --------- -------------------------------------------------------------------------------- aaaa 1 AAAMt+AABAAAO56AAA 1 4000-1-1 I N FE bbbb 2 AAAMt+AABAAAO56AAB 2 4000-1-1 I N FE 我们 对表和物化 视图 进行分析 然后 再次 查看 结果 SQL> analyze table ceshi_wuhua2 compute statistics; Table analyzed SQL> analyze table wuhua2 compute statistics; Table analyzed SQL> select * from mlog$_wuhua2; A B M_ROW$$ SEQUENCE$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ----- ------ -------------------- ---------- ----------- --------- --------- -------------------------------------------------------------------------------- aaaa 1 AAAMt+AABAAAO56AAA 1 4000-1-1 I N FE bbbb 2 AAAMt+AABAAAO56AAB 2 4000-1-1 I N FE 我们 可以观察 没有变化 我们 再次 对 基表进行 插入 SQL> insert into wuhua2 values ('cccc','00003'); 1 row inserted SQL> select * from mlog$_wuhua2; A B M_ROW$$ SEQUENCE$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ----- ------ -------------------- ---------- ----------- --------- --------- -------------------------------------------------------------------------------- aaaa 1 AAAMt+AABAAAO56AAA 1 4000-1-1 I N FE bbbb 2 AAAMt+AABAAAO56AAB 2 4000-1-1 I N FE cccc 3 AAAMt+AABAAAO56AAC 3 4000-1-1 I N FE SQL> commit; Commit complete SQL> select * from mlog$_wuhua2; A B M_ROW$$ SEQUENCE$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ----- ------ -------------------- ---------- ----------- --------- --------- -------------------------------------------------------------------------------- aaaa 1 AAAMt+AABAAAO56AAA 1 4000-1-1 I N FE bbbb 2 AAAMt+AABAAAO56AAB 2 4000-1-1 I N FE cccc 3 AAAMt+AABAAAO56AAC 3 4000-1-1 I N FE SQL> 上面的 列子 我们 可以 看出 我们 进行 COMMIT 前 和 COMMIT 后 查看视图物化日志 的 信息。大家 可以 对比一下 根据 上面的 操作 我们 来分析一下 日志的 信息 cccc 3 AAAMt+AABAAAO56AAC 3 4000-1-1 I N FE 就拿上面的 信息 进行分析 CCCC 表示 我们插入的 值, 3 表示 另一个 列 对应的值 AAAMt+AABAAAO56AAC 表示 序列 I 表示 是 插入 操作 我们 进行 UPDATE 操作 试一下 SQL> update wuhua2 set a='vvvv' where b='3'; 1 row updated SQL> select * from mlog$_wuhua2; A B M_ROW$$ SEQUENCE$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ----- ------ -------------------- ---------- ----------- --------- --------- -------------------------------------------------------------------------------- aaaa 1 AAAMt+AABAAAO56AAA 1 4000-1-1 I N FE bbbb 2 AAAMt+AABAAAO56AAB 2 4000-1-1 I N FE cccc 3 AAAMt+AABAAAO56AAC 3 4000-1-1 I N FE cccc 3 AAAMt+AABAAAO56AAC 4 4000-1-1 U U 02 vvvv 3 AAAMt+AABAAAO56AAC 5 4000-1-1 U N 02 SQL> commit; Commit complete SQL> select * from mlog$_wuhua2; A B M_ROW$$ SEQUENCE$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ ----- ------ -------------------- ---------- ----------- --------- --------- -------------------------------------------------------------------------------- aaaa 1 AAAMt+AABAAAO56AAA 1 4000-1-1 I N FE bbbb 2 AAAMt+AABAAAO56AAB 2 4000-1-1 I N FE cccc 3 AAAMt+AABAAAO56AAC 3 4000-1-1 I N FE cccc 3 AAAMt+AABAAAO56AAC 4 4000-1-1 U U 02 vvvv 3 AAAMt+AABAAAO56AAC 5 4000-1-1 U N 02 从 上面的 例子 我们仔细观察 可以看出 cccc 3 AAAMt+AABAAAO56AAC 4 4000-1-1 U U 02 vvvv 3 AAAMt+AABAAAO56AAC 5 4000-1-1 U N 02 第一行 是 修改前的值 第二行 是修改后的值 U 表示 是 UPDATE 操作
还剩18页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

zcj7219

贡献于2013-02-24

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf