Oracle10g Materialized 视图的改进

ZDNet软件频道　时间：2009-02-04　作者：盖国强 | eygle.com　我要评论()
本文关键词：盖国强 Oracle 软件

本文介绍了Oracle10g Materialized视图的改进。

今天Kamus在Gtalk上让我帮忙测试,是关于物化视图的:

truncate分区以后，物化视图快速刷新出错
drop分区以后，物化视图快速刷新出错

测试Oralce9i的情况:

SQL> create table T_PART
  2  (
  3    C1 NUMBER,
  4    C2 NUMBER,
  5    C3 NUMBER
  6  )
  7  partition by range (C2)
  8  (
  9    partition T_P2 values less than (20)
 10  ,
 11    partition T_P3 values less than (30)
 12  )
 13  ;

Table created.
SQL> insert into t_part values(1,2,3);

1 row created.

SQL> insert into t_part values(1,25,3);

1 row created.

SQL> insert into t_part values(1,18,3);

1 row created.

SQL> commit;

Commit complete.

SQL> create materialized view log on t_part with rowid;

Materialized view log created.

SQL> create materialized view mv_t_part refresh with rowid as select * from t_part;

Materialized view created.

SQL> select * from t_part;

        C1         C2         C3
---------- ---------- ----------
         1          2          3
         1         18          3
         1         25          3

SQL> select * from mv_t_part;

        C1         C2         C3
---------- ---------- ----------
         1          2          3
         1         18          3
         1         25          3

SQL> alter table t_part truncate partition t_p2;

Table truncated.

SQL> exec dbms_mview.refresh("mv_t_part","f");
BEGIN dbms_mview.refresh("mv_t_part","f"); END;

*
ERROR at line 1:
ORA-32313: REFRESH FAST of "SYS"."MV_T_PART" unsupported after PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

这里出现错误。

ORA-32313 REFRESH FAST of "string"."string" unsupported after PMOPs

Cause: A Partition Maintenance Operation (PMOP) has been performed on a detail table, and the specified materialized view does not support fast refersh after PMOPs.

Action: Use REFRESH COMPLETE. You can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.

再来测试Oracle10g的:

[Oracle@danaly ~]$ sqlplus eygle/eygle

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 13 22:10:15 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL> create table T_PART
  2    (
  3      C1 NUMBER,
  4      C2 NUMBER,
  5      C3 NUMBER
  6    )
  7    partition by range (C2)
  8    (
  9      partition T_P2 values less than (20)
 10    ,
 11      partition T_P3 values less than (30)
 12    )
 13  ;

Table created.

SQL> insert into t_part values(1,2,3);

1 row created.

SQL> insert into t_part values(1,25,3);

1 row created.

SQL> insert into t_part values(1,18,3);

1 row created.

SQL> create materialized view log on t_part with rowid;

Materialized view log created.

SQL> create materialized view mv_t_part refresh with rowid as select * from t_part;

Materialized view created.

SQL> select * from t_part;

        C1         C2         C3
---------- ---------- ----------
         1          2          3
         1         18          3
         1         25          3

SQL> select * from mv_t_part;

        C1         C2         C3
---------- ---------- ----------
         1          2          3
         1         18          3
         1         25          3

SQL> alter table t_part truncate partition t_p2;

Table truncated.

SQL> exec dbms_mview.refresh("mv_t_part","f");

PL/SQL procedure successfully completed.

看来在物化视图方面，Oracle10g的确已经增强。

查看本文来源

我要评论()

我有话要说订阅 RSS 探客网资源发送给朋友

上一篇:VB“超频”秘籍之隐藏的Variant变量

下一篇:DECLARE_SERIAL/IMPLEMENT_SERIAL宏

盖国强

Oracle

软件

最受关注的文章：

最新更新文章：

用户评论

评论列表
本篇文章共有条评论
点击这里查看更多评论>>

用户名

评论内容

发表时间

- 发表评论 -

推荐阅读

视频

微软嵌入式产品总监谈WEC 7的7大

《边看边读》第14期：读《Java加