Oracle10g Materialized View enhanced

« 赏画:展子虔之游春图 | Blog首页 | 2005-12-14:EMC DISK Warning »

今天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的确已经增强。

历史上的今天...
>> 2012-12-13文章:

Oracle云数据库初体验之三 - 账号和SQL Developer

>> 2011-12-13文章:

Oracle的表空间的存储管理与优化技术

>> 2008-12-13文章:

C:\WINDOWS\Installer文件夹的安全清理

>> 2007-12-13文章:

为什么Rac的心跳线不支持交叉线?

>> 2006-12-13文章:

"软件"、硬件都需要健康检查

2 Comments

玉面飞龙 | December 14, 2005 9:25 AM

你测试的fast refresh的时间花费了多少？还有主表truncate partition后mlog$_里面可有反映？ "Partition Change Tracking" 内部机制？

navy219 | March 3, 2010 3:08 PM

上述示例与我这边的不同，如下语句
exec dbms_mview.refresh('mv_t_part','f');
在oracle 9208 和1024都是执行不成功的，反而
exec dbms_mview.refresh('mv_t_part');
这样的语句可以执行成功，并刷新了物化视图。