物化视图学习笔记

物化视图
删除表后物化视图日志自动删除
SQL> CREATE MATERIALIZED VIEW LOG ON TT WITH ROWID,SEQUENCE(OBJECT_ID,OBJECT_NAME) INCLUDING NEW VALUES;

Materialized view log created.

SQL> EXEC DBMS_SNAPSHOT.REFRESH('MV1');
BEGIN DBMS_SNAPSHOT.REFRESH('MV1'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "HR"."TT" younger than last refresh
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> DROP MATERIALIZED VIEW MV1;

Materialized view dropped.   --删除旧的物化视图

SQL> CREATE MATERIALIZED VIEW MV1 REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT OBJECDT_ID,OBJECT_NAME FROM TT GROUP BY OBJECT_ID,OBJECT_NAME;
CREATE MATERIALIZED VIEW MV1 REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT OBJECDT_ID,OBJECT_NAME FROM TT GROUP BY OBJECT_ID,OBJECT_NAME
                                                                                   *
ERROR at line 1:
ORA-00904: "OBJECDT_ID": invalid identifier

SQL> C/OBJECDT/OBJECT
  1* CREATE MATERIALIZED VIEW MV1 REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT OBJECT_ID,OBJECT_NAME FROM TT GROUP BY OBJECT_ID,OBJECT_NAME
SQL> /

Materialized view created.

SQL> SELECT COUNT(*) FROM MV1;

  COUNT(*)
----------
      4258

SQL> INSERT INTO TT SELECT OBJECT_ID+1000,OBJECT_NAME,OBJECT_TYPE FROM TT WHERE ROWNUM

99 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM MV1;

  COUNT(*)
----------
      4357

SQL> DELETE TT;

4357 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM MV1;

  COUNT(*)
----------
      4357

SQL> EXEC DBMS_SNAPSHOT.REFRESH('MV1');
BEGIN DBMS_SNAPSHOT.REFRESH('MV1'); END;

*
ERROR at line 1:
ORA-12057: materialized view "HR"."MV1" is INVALID and must complete refresh
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_SNAPSHOT.REFRESH('MV1','C');

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM MV1;

  COUNT(*)
----------
         0
--如果对基表进行删除，修改操作，必须手动进行complete refresh
--insert 操作
SQL> insert into tt select object_id,object_name,object_type from all_objects;

4259 rows created.

SQL> select count(*) from mv1;

  COUNT(*)
----------
         0

SQL> commit;

Commit complete.

SQL>select count(*) from mv1;

  COUNT(*)
----------
      4259
--update 操作
-------------- ------------------------------
          5453 ALL_OUTLINES
          5455 DBA_OUTLINES
          5495 ORA_DICT_OBJ_OWNER
SQL> l
  1* update tt set object_id=5453 ,object_name=ALL_OUTLINES where object_id=5455
SQL> update tt set object_id=5453 ,object_name='ALL_OUTLINES' where object_id=5455;

1 row updated.

SQL> select count(*) from mv1;

  COUNT(*)
----------
      4160

SQL> commit;

Commit complete.

SQL> select count(*) from mv1;

  COUNT(*)
----------
      4160

SQL> exec dbms_snapshot.refresh('MV1','C');

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)FROM MV1;

  COUNT(*)
----------
      4159

--物化视图日志
SQL> desc mlog$_tt
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(30)
 M_ROW$$                                            VARCHAR2(255)
 SEQUENCE$$                                         NUMBER
 SNAPTIME$$                                         DATE
 DMLTYPE$$                                          VARCHAR2(1)
 OLD_NEW$$                                          VARCHAR2(1)
 CHANGE_VECTOR$$                                    RAW(255)

SQL> select * from mlog$_tt;

no rows selected

--insert操作，未commit时
SQL> insert into tt select object_id+1001,object_name,object_type from tt where rownum

2 rows created.

SQL> select count(*) from mlog$_tt;

  COUNT(*)
----------
         2
 OBJECT_ID OBJECT_NAME          M_ROW$$              SEQUENCE$$ SNAPTIME$ D O CHANGE_VECTOR$$
---------- -------------------- -------------------- ---------- --------- - - --------------------
      7027 WPG_DOCLOAD          AAAD/3AAFAAAACPAAA        37102 01-JAN-00 I N FE
      7028 DBMS_DEBUG_JDWP      AAAD/3AAFAAAACPAAB        37103 01-JAN-00 I N FE
SQL> commit;

Commit complete.

SQL> select count(*) from mlog$_tt;

  COUNT(*)
----------
         0

 

--可更新物化视图
SQL> update mv1 set object_id=1000 where rownum
update mv1 set object_id=1000 where rownum
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

 

SQL> truncate table mv1;

Table truncated.

 

SQL> create materialized view mv1 refresh fast on commit enable query rewrite as select * from tt;
create materialized view mv1 refresh fast on commit enable query rewrite as select * from tt
                                                                                          *
ERROR at line 1:
ORA-12014: table 'TT' does not contain a primary key constraint

SQL> create materialized view mv1 refresh fast on commit with rowid enable query rewrite as select * from tt;

Materialized view created.

SQL> create materialized view mv1 for update as select object_id,object_name from tt group by object_id,object_name;
create materialized view mv1 for update as select object_id,object_name from tt group by object_id,object_name
                                                                             *
ERROR at line 1:
ORA-12013: updatable materialized views must be simple enough to do fast refresh

SQL> create materialized view mv1 refresh fast with rowid for update enable query rewrite as select * from tt;

Materialized view created.

阅读(8090) | 评论(0) | 转发(3) |

0

上一篇：关于drop操作对role的影响

下一篇：foreign key的一些总结

相关热门文章

  • ORACLE 变异表解决方法
  • ORA-00600错误分析
  • 初识ORACLE的审计功能
  • Oracle的OLEDB
  • 学ORACLE随笔
时间： 2024-08-28 12:33:20

物化视图学习笔记的相关文章

小波说雨燕 第三季 构建 swift UI 之 UI组件集－视图集（四）Alert View视图 学习笔记

当我们的应用电量不足的时候,就需要警告提示,那么我们可以用Alert View视图 实现:

MySQL VIEW(视图)学习笔记

视图的作用: ■视图可以让查询变得很清楚: 如果您要找的数据存放在三张关系表里面,查看数据的时候,你就要写个联合查询了.换种方法,我把联合查询的数据放到视图里面,这样查询起来是不是更方便呢? ■保护数据库的重要数据,给不同的人看不同的数据: 假如您让别人帮您开发一套系统,但是你又想把真正表的暴露出来,这个时候视图是不是最好的选择呢? 视图的类型: mysql的视图有三种类型:MERGE.TEMPTABLE.UNDEFINED.如果没有ALGORITHM子句,默认算法是UNDEFINED(未定义的

mysql视图学习笔记记录

创建视图 在MySQL中,创建视图的完整语法如下: CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]     VIEW view_name [(column_list)]     AS select_statement     [WITH [CASCADED | LOCAL] CHECK OPTION] 其对应的语法变量信息如下: [OR REPLACE]     中括号中的OR REPLACE关键字是可选的.如果

小波说雨燕 第三季 构建 swift UI 之 UI组件集－视图集（五）Image View视图 学习笔记

留下两个问题:1.后面涉及到的异常不知道原因.2.动态图片到了程序里面就不动了. 然后: 上面是有问题的,下面是没有问题的了. 代码(另外简单写的代码,纠正了那个错误): 1 import UIKit 2 3 class ViewController: UIViewController{ 4 5 @IBOutlet weak var imageView: UIImageView! 6 @IBAction func button(sender: UIButton) { 7 imageView.an

Mysql学习笔记（十一）临时表+视图

原文:Mysql学习笔记(十一)临时表+视图 学习内容: 临时表和视图的基本操作... 临时表与视图的使用范围... 1.临时表   临时表:临时表,想必大家都知道这个概念的存在...但是我们什么时候应该使用到临时表呢?当一个数据库存在着大量的数据的时候,我们想要获取到这个数据集合的一个子集,那么我们就可以使用临时表来保存我们想要的数据..然后对临时表进行操作就可以了...使用临时表必然是有原因的..使用临时表会加快数据库的查询性能.... create temporary table tmp_

小波说雨燕 第三季 构建 swift UI 之 UI组件集－视图集（七）Progress View进度条 学习笔记

小波说雨燕 第三季 构建 swift UI 之 UI组件集-视图集(七)Progress View进度条 学习笔记 1 import UIKit 2 3 class ViewController: UIViewController{ 4 5 @IBOutlet weak var progress: UIProgressView! 6 7 let operationQueue = NSOperationQueue() 8 9 var 完成进度: Int = 0{ 10 didSet(oldValu

[20120809]学习物化视图(补充).txt

[20120809]学习物化视图(补充).txt 1.测试环境: SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0

[20120808]学习物化视图.txt

[20120808]学习物化视图.txt 从来没有使用过物化视图,今天学习看看,主要是练习,我的测试基本按照如下链接的例子: http://uhesse.com/2009/07/08/brief-introduction-into-materialized-views/ SQL> select * from v$version ; BANNER ----------------------------------------------------------------------------

物化视图实现的特殊数据复制(r11笔记第42天)

  今天开发同事碰到一个有些复杂的数据复制需求,想让我帮忙看看能否实现,当然猛一听需求是不可能实现的.不过还是耐着性子和他们讨论了一下,不过我想了下,似乎还是有改进的余地,也算是拨云见雾吧.   目前有一个表做了拆分,即分库分表.在统计业务中还是需要把数据整合起来查询.大体就是下面的架构方式. 源端是一些分库,存在一些不同的用户,里面存放着相同结构的表.数据根据拆分规则进入不同的分库. 目标端是统计业务所用,没有使用OGG,而直接使用物化视图的方式做了数据刷新复制,当然目标端由此就有了相同数量的