Oracle10g Materialized View enhanced

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

Oracle10g Materialized View enhanced

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2005/12/oracle10g_materialized_view_enhanced.html

站内相关文章|Related Articles

Oracle10g新进程 MMON 和 MMNL

鸡肋-ORACLE10g自动收集CBO统计信息

安装了Oracle10g 10.2.0.3 感受众多BUG

Oracle中独一无二的Cache对象

Oracle10g中过程(PROCEDURE )重建的增强

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

-----
这篇【Oracle10g Materialized View enhanced】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key

相关文章	随机文章
Oracle10g新进程 MMON 和 MMNL 鸡肋-ORACLE10g自动收集CBO统计信息安装了Oracle10g 10.2.0.3 感受众多BUG Oracle中独一无二的Cache对象 Oracle10g中过程(PROCEDURE )重建的增强	Windows无法显示隐藏文件夹之问题解决光纤通道故障导致数据库崩溃感谢d.c.b.a 新书第六章定稿 Oracle中模拟及修复数据块损坏靓颖来公司的访谈-靓颖靓照

网上相关主题:

留言 (1)

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

Posted by: 玉面飞龙 at December 14, 2005 9:25 AM

www.eygle.com

Oracle10g Materialized View enhanced

留言 (1)

发表留言: