Oracle物化视图详解


ORACLE 中的物化(实体)视图 物化视图是包括一个查询结果的数据库对像,它是远程数据的的 本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基 于远程表的数据,也可以称为快照。 物化视图可以查询表,视图和其它的物化视图。 通常情况下,物化视图被称为主表(在复制期间)或明细表(在 数据仓库中)。 对于复制,物化视图允许你在本地维护远程数据的副本,这些副本 是只读的。如果你想修改本地副本,必须用高级复制的功能。当你想 从一个表或视图中抽取数据时,你可以用从物化视图中抽取。 对于数据仓库,创建的物化视图通常情况下是聚合视图,单一表 聚合视图和连接视图。 本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。 在复制环境下,创建的物化视图通常情况下主键,rowid,和子查询 视图。 1.主键物化视图: 下面的语法在远程数据库表 emp 上创建主键物化视图 SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/48 WITH PRIMARY KEY 1 AS SELECT * FROM emp@remote_db; Materialized view created. 注意:当用 FAST 选项创建物化视图,必须创建基于主表的视 图日志,如下: SQL> CREATE MATERIALIZED VIEW LOG ON emp; Materialized view log created. 2.Rowid 物化视图 下面的语法在远程数据库表 emp 上创建 Rowid 物化视图 SQL> CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID AS SELECT * FROM emp@remote_db; Materialized view log created. 3.子查询物化视图 下面的语法在远程数据库表 emp 上创建基于 emp 和 dept 表的 子查询物化视图 SQL> CREATE MATERIALIZED VIEW mv_empdept AS SELECT * FROM emp@remote_db e WHERE EXISTS (SELECT * FROM dept@remote_db d WHERE e.dept_no = d.dept_no) Materialized view log created. REFRESH 子句 2 [refresh [fast|complete|force] [on demand | commit] [start with date] [next date] [with {primary key|rowid}]] Refresh 选项说明: a. oracle 用刷新方法在物化视图中刷新数据. b. 是基于主键还是基于 rowid 的物化视图 c. 物化视图的刷新时间和间隔刷新时间 Refresh 方法-FAST 子句 增量刷新用物化视图日志(参照上面所述)来发送主表已经修改 的数据行到物化视图中.如果指定 REFRESH FAST 子句,那么应该对 主表创建物化视图日志 SQL> CREATE MATERIALIZED VIEW LOG ON emp; Materialized view log created. 对于增量刷新选项,如果在子查询中存在分析函数,则物化视图 不起作用。 Refresh 方法- COMPLETE 子句 完全刷新重新生成整个视图,如果请求完全刷新,oracle 会完成 完全刷新即使增量刷新可用。 3 Refresh Method – FORCE 子句 当指定 FORCE 子句,如果增量刷新可用 Oracle 将完成增量刷新, 否则将完成完全刷新,如果不指定刷新方法(FAST, COMPLETE, or FORCE),Force 选项是默认选项 主键和 ROWD 子句 WITH PRIMARY KEY 选项生成主键物化视图,也就是说物化视图 是基于主表的主键,而不是 ROWID(对应于 ROWID 子句). PRIMARY KEY 是默认选项,为了生成 PRIMARY KEY 子句,应该在主表上定义 主键,否则应该用基于 ROWID 的物化视图. 主键物化视图允许识别物化视图主表而不影响物化视图增量刷新 的可用性。 Rowid 物化视图只有一个单一的主表,不能包括下面任何一项: Distinct   或者聚合函数. Group by  ,子查询,连接和 SET 操作 刷新时间 START WITH 子句通知数据库完成从主表到本地表第一次复制的 时间,应该及时估计下一次运行的时间点, NEXT 子句说明了刷新的 间隔时间. SQL> CREATE MATERIALIZED VIEW mv_emp_pk 4 REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 2 WITH PRIMARY KEY AS SELECT * FROM emp@remote_db; Materialized view created. 在上面的例子中,物化视图数据的第一个副本在创建时生成,以后每 两天刷新一次. 总结 物化视图提供了可伸缩的基于主键或 ROWID 的视图,指定了刷新方 法和自动刷新的时间。 物化视图的效率 有两个表:人员表 A001 有 10 万数据,机构表 B001 有 5 千数据 建普通视图: create or replace view test_v as select * from A001 a ,B001 B where a.a001721=b.orguid 建一个物化视图 5 create MATERIALIZED view test_v_m as select * from A001 a ,B001 B where a.a001721=b.orguid 比较效率: select * from test_v where A001001 like '%军%' or b001003 like '%001006%' or A001011 like '%1970%' ;用 时 10 秒多 from test_v_m where A001001 like '%军%' or b001003 like '%001006%' or A001011 like '%1970%' ;用时 0.125 秒 对查询来说,物化视图的效率是普通视图的 10/0.125=80 倍;相 当高啊! 6 创建多表关联的实体化视图 某些实体化视图包含多表关联,但不包含聚合操作 (例如 sum()) 例如 Example 8-4 实体化视图是在 sales 表和 times,customers 三张表的关联的基础上 创建的。创建这种类型的 实体化视图的好处是预先处理了代价高昂 的关联操作。在任意类型的 DML 操作之后,使用 fast refresh 的方 式来刷新仅包含表关联的实体化视图是可以实现的 。 仅包含表关联的实体化视图同样支持在两种情况下刷新: ON COMMIT 和 ON DEMAND.在 ON COMMIT 下,当基表发生 DML 操作并提交 commit 的时候,实体化视图被刷新。 Oracle 不允许 实体化视图中有 self-jions 的存在。 假如指定刷新方式为 REFRESH FAST, oracle 执行附加的校验,以确定 fast refresh 能被执 行。这些附加的检查包括: 1 每个基表下必须创建相应的 materialized view log 2 每个基表的 rowid 必须在实体化视图的 select 语句中出现 3 如果存在外联接,在 where 子句不能有任何的 selections.并且,所 有的关联必须以 AND 相连,并且用 “=”操作符。 4 如果存在外联接,唯一性约束必须存在于外联的 inner table 的相关 列中。 7 为了提高刷新的速度,必须在实体化视图上创建基表的 rowed 所在 列的 索引。 Example 8-4 Materialized View Containing Only Joins CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID; CREATE MATERIALIZED VIEW LOG ON times WITH ROWID; CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID; CREATE MATERIALIZED VIEW detail_sales_mv PARALLEL BUILD IMMEDIATE REFRESH FAST 8 AS SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid", c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id FROM sales s, times t, customers c WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+); 在这个示例中,为了执行 fast refresh , 唯一性约束必须存在于 c.cust_id and t.time_id. 必须创建 sales_rid, times_rid, and customers_rid 各自的索引,如此可以提高刷新的速度。 CREATE INDEX mv_ix_salesrid ON detail_sales_mv("sales_rid"); Materialized Views with Aggregates 9 创建聚合类型的实体化视图。 在数据仓库中,实体化视图中经常会包括一些聚合操作。为了能够进 行快速刷新(fast refersh) ,在 select 语句中必须包含所有在 group by 中出现的列名,并且必须有一个 count(*) ,所有在 gorup by 中出现 的列都必须有一个 count(列名)出现在 select 中。同样,所有于实体 化视图相关的表上都必须创建实体化和视图日志。有效的聚合函数包 括: SUM , COUNT(x) , COUNT(*) , AVG , VARIANCE , STDDEV , 10 MIN , and MAX , 被聚合的值可以是任何的 sql 表达式。 Fast refresh 一个包含关联和聚合的实体化视图是可能的。但有如下限制: 1 实体化视图中不能包含非复用的表达式 ( 具有实时特征的 ) 。比如 SYSDATE and ROWNUM. 2 实体化视图中不能包含 RAW or LONG 数据类型。 3 11 实体化视图相关的表必须创建实体化视图日志,并且满足如下条件 包含被实体化视图引用到的所有的列。 指定 with rowed 和 INCLUDING NEW VALUES. 如果表的预期操作是 inserts/direct-loads, deletes, and updates 的混合,指定 SEQUENCE 子 句只用 SUM , COUNT , AVG , STDDEV , VARIANCE , MIN and MAX 12 支持 fast refresh. 对于每个聚合 agg(expr), 必须相应写一个 count(expr) 与之对应 如果 VARIANCE(expr)or STDDEV(expr) 被指定,那么 COUNT(expr)and SUM(expr) 必须被指定。Oracle 推荐 SUM(expr *expr) 也被指定。在 select 列表中必须包含所有 group by 中出现的列.如果实体化视图有如下的一点,那么 fast refresh 只支持 常规 DML inserts 和直接路径的装载 ( direct loads ) a 实体化视图包含 MIN 或者 MAX 13 b 实体化视图包含 SUM(expr) 但是没有 COUNT(expr) c 实体化视图中不包含 COUNT(*)如上的实体化视图称之为 insert-only materialized view. 包含 MAX or MIN 实体化视图在 delete 或者混合的 DML 操作之后可以是 fast refresh,条件是视图中没有 where 子句。 (10g 以上适用 ) 如果没有外联接,在 where 子句中可以包含有选择性的条件和关联 如果实体化视图中包含外联接,fast refresh 只支持在 outer table 被 修改的情况。同样,唯一性约束必须存在于 inner join table 中。如果有外联,所有的关联条件都必须用 AND 连 接和必须用 = 操作符。 Example 8-1 Example 1: Creating a Materialized View CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, ROWID 14 (prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW product_sales_mv PCTFREE 0 TABLESPACE demo STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0) BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales, COUNT(*) AS cnt, COUNT(s.amount_sold) AS cnt_amt 15 FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_name; Example 8-3 Example 3: Creating a Materialized View CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW sum_sales PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT s.prod_id, s.time_id, COUNT(*) AS count_grp, SUM(s.amount_sold) AS sum_dollar_sales, COUNT(s.amount_sold) AS count_dollar_sales, SUM(s.quantity_sold) AS sum_quantity_sales, 16 COUNT(s.quantity_sold) AS count_quantity_sales FROM sales s GROUP BY s.prod_id, s.time_id; Oracle 的实体化视图(MVIEW)的深入研究之一 从 Oracle 8i 开始提供了实体化视图, 能过预先计算好的中间表来提 高应用的访问速度, 在特定的情况下是很有用的一项技术. 另外实体 化视图还可用于数据复制, 在这个上面的应用越来越多. MVIEW 中 经常跗以遇到刷新很慢的情况, 如何提高呢? 首先来研究一下刷新 的过程. 下面是用来创建演示表的角本: CREATE TABLE T_MVLOG (COL1 VARCHAR2(20)); CREATE MATERIALIZED VIEW LOG ON T_MVLOG WITH ROWID, sequence; CREATE MATERIALIZED VIEW MV_T_MVLOG REFRESH FAST WITH ROWID AS 17 SELECT ROWID R_ID, A.* FROM T_MVLOG A; 我们对 DBMS_MVIEW.REFRESH 作一个 SQL_TRACE, 在这个 例子中, 我在基表中插入了一打记录, 然后作跟踪的. 可以看到第一 步为: update "ANYSQL"."MLOG $_T_MVLOG" set snaptime $ $ = :1 where snaptime $ $ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS') 第二步, 取得在这段时间内发生修改的每一行的 ROWID SELECT DISTINCT M_ROW $ $ FROM ( SELECT M_ROW $ $ FROM "ANYSQL"."MLOG $_T_MVLOG" MLOG $ WHERE "SNAPTIME $ $" > :1 AND ("DMLTYPE $ $" != 'I') ) LOG $ WHERE (M_ROW $ $) NOT IN ( SELECT ROWID FROM "T_MVLOG" "MAS_TAB $" WHERE MAS_TAB $.ROWID = LOG $.M_ROW $ $ ) 18 第三步, 取得刷新后的值 SELECT CURRENT $."R_ID", CURRENT $."COL1", ROWIDTOCHAR(CURRENT $.ROWID) M_ROW $ $ FROM ( SELECT "A".ROWID "R_ID","A"."COL1" "COL1" FROM "T_MVLOG" "A" ) CURRENT $, ( SELECT DISTINCT M_ROW $ $ FROM "ANYSQL"."MLOG $_T_MVLOG" MLOG $ WHERE "SNAPTIME $ $" > :1 AND ("DMLTYPE $ $" != 'D') ) LOG $ WHERE CURRENT $.ROWID = LOG $.M_ROW $ $ 第四步, 对 MVIEW 进行插入 INSERT INTO "ANYSQL"."MV_T_MVLOG" ("R_ID","COL1","M_ROW $ $") 19 VALUES (:1,:2,:3) 最后一步, 删除 MVLOG 中的值 delete from "ANYSQL"."MLOG $_T_MVLOG" where snaptime $ $ <= :1 从这外过程来看, 可以调的方法有四个, 首先尽量使用快速刷新, 提高刷新频率, 其次可以在 MLOG $_T_MVLOG 这个表的 snaptime $ $ 字段上建索引, 第三为刷新的过程设定会话级的 DB_FILE_MULTIBLOCK_READ_COUNT 以及 SORT_AREA_SIZE 等参数, 第四选择时间对 MLOG $_T_MVLOG 这个表进行重组以减 少表的大小. 当在一个表上建了物化视图的日志(Materialized View Log)后, 所有 的 DML 操作都会被相应地记录到物化视图日志表(MLOG $_)中, 如 果想对这个表进行操作, 但不想这些操作被记录到日志(MVIEW LOG)中, 应当怎么办呢? 在 DBMS_MVIEW 包中有两个过程可以用 来完成这个要求. 这里我们需要打开两个会话, 其中一个会话以 DBA 的身份登陆(Session DBA), 另一个会话随便了(Session USER), 按如下次序来进行操作: 在 Session USER 中先运行以下语句去看一下 MVIEW LOG 表中 20 有多少条记录: SQL> SELECT count(*) FROM MLOG $_T_REORG; COUNT(*) ---------- 0 在 Session DBA 中运行 BEGIN_TABLE_REORGANIZATION 过 程开始维护, 记得执行完后要运行 COMMIT 命令, 否则会阻塞(Block) 别的进程: SQL> exec dbms_mview.begin_table_reorganization('ANYSQL','T_REORG'); PL/SQL procedure successfully completed. 在 Session USER 中先运行 DML 直接修改表的数据, 提交后看一 下 MVIEW LOG 表中的记录有没有增加: SQL> insert into t_reorg select * from tab; 13 rows created. 21 SQL> COMMIT; Commit complete. SQL> SELECT count(*) FROM MLOG $_T_REORG; COUNT(*) ---------- 0 在 Session DBA 中运行 END_TABLE_REORGANIZATION 过程 结束维护, 记得执行完后要运行 COMMIT 命令, 否则会阻塞(Block) 别的进程: SQL> exec dbms_mview.end_table_reorganization('ANYSQL','T_REORG'); PL/SQL procedure successfully completed. 在 Session USER 中先运行一些 DML 语句, 检查一下 MVIEW LOG 表中的记录数是不是增多了: SQL> delete t_reorg; 22 13 rows deleted. SQL> commit; Commit complete. SQL> SELECT count(*) FROM MLOG $_T_REORG; COUNT(*) ---------- 13 在这儿说了一大堆, 其实是如何让表不生成实体化视图日志的 问题. 在 Oracle 中创建视图时, 如果我们用了"*"符号, 会被自动地根据当 时表的定义扩展成字段列表, 在后面再加列时, 新的列不会自动出现 在视图的定义中, 直到你重建视图为止. 那么在 MVIEW 中呢, 通过 一个不经意的操作, 发现一个有趣的问题. 总之, 不要随便地在实体 视图的定义中使用"*"号. 下面我们在一个表上建两个实体化视图, 角本如下: 23 CREATE TABLE T_MVTEST AS SELECT * FROM TAB; CREATE MATERIALIZED VIEW LOG ON T_MVTEST WITH ROWID,SEQUENCE; CREATE MATERIALIZED VIEW MV_TEST_STAR REFRESH FAST WITH ROWID AS SELECT ROWID RID, A.* FROM T_MVTEST A; CREATE MATERIALIZED VIEW MV_TEST_NOSTAR REFRESH FAST WITH ROWID AS SELECT ROWID RID, A.TNAME, A.TABTYPE, A.CLUSTERID FROM T_MVTEST A; 接下来对表作一个增加列的操作. SQL> ALTER TABLE T_MVTEST ADD COL4 VARCHAR2(20); Table altered. 接下来来完全刷新两上实体化视图, 看看运行情况: SQL> EXEC DBMS_MVIEW.REFRESH('MV_TEST_STAR', 'COMPLETE'); BEGIN DBMS_MVIEW.REFRESH('MV_TEST_STAR', 'COMPLETE'); END; 24 * ERROR at line 1: ORA-12018: following error encountered during code generation for "ANYSQL"."MV_TEST_STAR" ORA-00904: "COL4": invalid identifier ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430 ORA-06512: at line 1 SQL> EXEC DBMS_MVIEW.REFRESH('MV_TEST_NOSTAR', 'COMPLETE'); PL/SQL procedure successfully completed. 这是因为在进行全部方式的刷新时, 实体化视图的定义根据当 前表的定义被展开了, 将会要刷新新增的"COL4"这个列, 而在表 "MV_TEST_STAR"中现在没有这个例, 所以刷新失败, 解决的方法 呢是在, 这个视图中手工加一个列: SQL> ALTER TABLE MV_TEST_STAR ADD COL4 NUMBER; 25 Table altered. SQL> EXEC DBMS_MVIEW.REFRESH('MV_TEST_STAR', 'COMPLETE'); PL/SQL procedure successfully completed. 可以这样解决结果还算好了. 现在对第一页中基表进行移动(Move)操作, 会发现不能进行快速刷 新, 必须进行全部(Complete)刷新才行. 如下所示: SQL> ALTER TABLE T_MVLOG MOVE; Table altered. SQL> EXEC DBMS_MVIEW.REFRESH('MV_T_MVLOG','FAST'); BEGIN DBMS_MVIEW.REFRESH('MV_T_MVLOG','FAST'); END; * ERROR at line 1: ORA-12034: materialized view log on "ANYSQL"."T_MVLOG" 26 younger than last refresh ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841 ORA-06512: at line 1 为什么失败, 可以仔细分析一下基于 ROWID 的 MVIEW LOG 被 刷新的过程, 当基表移动后, ROWID 的值变了, 因此不能再继续支持 主表的 UPDATE/DELETE 这样的操作了. 下面将这个实体化视图重 新定义成基于主键(Primary Key)的, 首先删除现有的实体化视图及日 志, 再为表加一个主键, 重新创建实体化视图 : SQL> alter table t_mvlog modify col1 not null; Table altered. SQL> alter table t_mvlog add primary key (col1); Table altered. SQL> create materialized view log on t_mvlog with primary key, sequence; 27 Materialized view log created. SQL> CREATE MATERIALIZED VIEW MV_T_MVLOG 2 REFRESH FAST WITH PRIMARY KEY 3 AS SELECT ROWID R_ID, A.* FROM T_MVLOG A; Materialized view created. 再对基表作移动(Move)操作后, 就可以进行增量刷新了. SQL> alter table t_mvlog move; Table altered. SQL> exec dbms_mview.refresh('MV_T_MVLOG','FAST'); PL/SQL procedure successfully completed. 选择基于 ROWID 还是基于主键(Primary Key)的实体化视图日志, 还是很重要的. 28 创建实体化视图产生 ORA-600 错误的解决方法 晚上测试实体化视图复制,测试环境中的 master site 是 oracle10g, mv site 是 oracle9201,当在 mv site 上创建快速刷新的实体化视图时, 报 ora-600 错误。 sql> create materialized view kamus.account2004 refresh fast with primary key as select * from kamus.account2004@orcl; ora-00600: internal error code, arguments: 查 metalink,发现又是一个 bug,这 个 bug 只有当在 oracle8 或者 9 中 创建基于 oracle10g 的实体化视图时才会发生。 原因: oracle10g 的 master table 中创建主键时候显式指定了主键的名称。如 下 alter table table_name add constraint < constraint name> primary key (< col>); 解决方法: 删除这个主键,然后创建一个不指定名称的主键,由 oracle 自动命名, 如下 alter table account2004 add primary key(occurtime, acctid, currencyid); 这样产生的主键名称就变成 sys_cxxxx。 之后重新在 mv site 上创建实体化视图,成功。 29 定位导致物化视图无法快速刷新的原因 物化视图的快速刷新采用了增量的机制,在刷新时,只针对基表上发 生变化的数据进行刷新。因此快速刷新是物化视图刷新方式的首选。 但是快速刷新具有较多的约束,而且对于采用 ON COMMIT 模式进 行快速刷新的物化视图更是如此。对于包含聚集和包含连接的物化视 图的快速刷新机制并不相同,而且对于多层嵌套的物化视图的快速刷 新更是有额外的要求。 如此多的限制一般很难记全,当建立物化视图失败时,Oracle 给出的 错误信息又过于简单,有时无法使你准确定位到问题的原因。 Oracle 提供的 DBMS_MVIEW.EXPLAIN_MVIEW 过程可以帮助你快 速定位问题的原因。下面通过一个例子来说明,如果通过这个过程来 解决问题。 建立一个快速刷新的嵌套物化视图: SQL> CREATE TABLE B (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30)); 表已创建。 SQL> CREATE TABLE C (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30)); 表已创建。 SQL> CREATE TABLE A (ID NUMBER, BID NUMBER, CID NUMBER, NUM NUMBER, 2 CONSTRAINT FK_A_B_BID FOREIGN KEY (BID) 30 REFERENCES B(ID), 3 CONSTRAINT FK_A_C_BID FOREIGN KEY (CID) REFERENCES C(ID)); 表已创建。 SQL> INSERT INTO B SELECT ROWNUM, 'B'||ROWNUM FROM USER_TABLES WHERE ROWNUM <= 6; 已创建 6 行。 SQL> INSERT INTO C SELECT ROWNUM, 'C'||ROWNUM FROM USER_TABLES WHERE ROWNUM <= 4; 已创建 4 行。 SQL> INSERT INTO A SELECT ROWNUM, TRUNC((ROWNUM - 1)/2) + 1, TRUNC((ROWNUM - 1)/3) + 1, ROWNUM 2 FROM USER_TABLES 3 WHERE ROWNUM <= 12; 已创建 12 行。 SQL> COMMIT; 提交完成。 上面建立好基表,下面建立第一层物化视图。 SQL> CREATE MATERIALIZED VIEW LOG ON A WITH ROWID; 实体化视图日志已创建。 SQL> CREATE MATERIALIZED VIEW LOG ON B WITH ROWID; 实体化视图日志已创建。 31 SQL> CREATE MATERIALIZED VIEW LOG ON C WITH ROWID; 实体化视图日志已创建。 SQL> CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS 2 SELECT C.ID CID, C.NAME CNAME, B.ID BID, B.NAME BNAME, A.NUM, 3 A.ROWID AROWID, B.ROWID BROWID, C.ROWID CROWID 4 FROM A, B, C WHERE A.BID = B.ID AND A.CID = C.ID; 实体化视图已创建。 第一次物化视图已经建立成功,下面建立嵌套物化视图: SQL> CREATE MATERIALIZED VIEW LOG ON MV_ABC WITH ROWID (BNAME, CNAME, NUM) INCLUDING NEW VALUES; 实体化视图日志已创建。 SQL> CREATE MATERIALIZED VIEW MV_MV_ABC REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS 2 SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROM MV_ABC 3 GROUP BY CNAME, BNAME; SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROM MV_ABC * ERROR 位于第 2 行: 32 ORA-12053: 这不是一个有效的嵌套实体化视图 错误出现了,不过错误的描述包含的信息量并不大。我们看看 Oracle 的文档上是如何描述这个错误的。 ORA-12053 this is not a valid nested materialized view Cause: The list of objects in the FROM clause of the definition of this materialized view had some dependencies upon each other. Action: Refer to the documentation to see which types of nesting are valid. 文档上的描述也是十分笼统的,并没有指出具体问题所在。 接下来,我们通过使用 DBMS_MVIEW.EXPLAIN_MVIEW 过程来定 位错误。 使用 EXPLAIN_MVIEW 过程首先要建立 MV_CAPABILITIES_TABLE 表,建表的脚步是 $ORACLE_HOME/rdbms/admin/utlxmv.sql。(EXPLAIN_MVIEW 过程 是两个过程的重载,一个输出到 MV_CAPABILITIES_TABLE 表,另 一个以 PL/SQL 的 VARRAY 格式输出,为了简单起见,我们建立 MV_CAPABILITIES_TABLE 表)。 SQL> @?rdbmsadminutlxmv.sql 表已创建。 下面简单研究一下 EXPLAIN_MVIEW 过程。 DBMS_MVIEW.EXPLAIN_MVIEW(mv IN VARCHAR2, Statement_id IN VARCHAR2:= NULL); 33 该过程可以输入已经存在的物化视图名称(或 USER_NAME.MV_NAME),也可输入建立物化视图的查询语句。另 外一个参数 STATEMENT_ID 输入一个语句 ID,为了标识出表中对应 的记录。 SQL> BEGIN 2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROM MV_ABC 3 GROUP BY CNAME, BNAME', 'MV_MV_ABC'); 4 END; 5 / PL/SQL 过程已成功完成。 SQL> SELECT CAPABILITY_NAME, RELATED_TEXT, MSGTXT FROM MV_CAPABILITIES_TABLE 2 WHERE STATEMENT_ID = 'MV_MV_ABC' AND POSSIBLE = 'N' AND CAPABILITY_NAME NOT LIKE '%PCT%'; CAPABILITY_NAME RELATED_TEXT MSGTXT ------------------------------ --------------- -------------------------------------------------- REFRESH_FAST_AFTER_ONETAB_DML SUM_NUM 使 用 SUM(expr) 时, 未提供 COUNT(expr) 34 REFRESH_FAST_AFTER_ANY_DML YANGTK.MV_ABC mv 日志没有序列号 REFRESH_FAST_AFTER_ANY_DML 查看 禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因 根据上面的信息,已经可以确定问题的原因了,对于聚集物化视图, 使用了 SUM(COLUMN),但是没有包括 COUNT(COLUMN)。 修改物化视图,重新建立: SQL> CREATE MATERIALIZED VIEW MV_MV_ABC REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS 2 SELECT CNAME, BNAME, COUNT(*) COUNT, COUNT(NUM) NUM_COUNT, SUM(NUM) SUM_NUM FROM MV_ABC 3 GROUP BY CNAME, BNAME; 实体化视图已创建。 35
还剩34页未读

继续阅读

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

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

需要 15 金币 [ 分享pdf获得金币 ] 6 人已下载

下载pdf

pdf贡献者

luojuncsu

贡献于2011-04-11

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