eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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

Oracle10g Materialized View enhanced

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

  1. truncate分区以后,物化视图快速刷新出错
  2. 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文章:
    >> 2011-12-13文章:
    >> 2008-12-13文章:
    >> 2007-12-13文章:
    >> 2006-12-13文章:

无觅

By eygle on 2005-12-13 23:14 | Comments (2) | Advanced | Oracle12c/11g | 577 |

2 Comments

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

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


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com