开发者社区> jeanron100> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

物化视图学习笔记

简介: 物化视图 删除表后物化视图日志自动删除 SQL> CREATE MATERIALIZED VIEW LOG ON TT WITH ROWID,SEQUENCE(OBJECT_ID,OBJECT_NAME) INCLUDING NEW VALUES; Materialized view log created.
+关注继续查看
物化视图
删除表后物化视图日志自动删除
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) |

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
全方位解读服务网格(Service Mesh)的背景和概念
为了解决微服务框架的侵入性问题,我们引入服务网格。
1219 0
PolarDB 并行查询的前世今生
本文会深入介绍PolarDB MySQL在并行查询这一企业级查询加速特性上做的技术探索、形态演进和相关组件的实现原理,所涉及功能随PolarDB MySQL 8.0.2版本上线。
486 0
企业内部应用接入钉钉获取部门及人员信息
企业内部应用接入钉钉,同时通过API获取当前企业下部门及人员信息
1170 0
MySQL模糊查询再也用不着 like+% 了!
我们都知道 InnoDB 在模糊查询数据时使用 "%xx" 会导致索引失效,但有时需求就是如此,类似这样的需求还有很多,例如,搜索引擎需要根基用户数据的关键字进行全文查找,电子商务网站需要根据用户的查询条件,在可能需要在商品的详细介绍中进行查找,这些都不是B+树索引能很好完成的工作。 通过数值比较,范围过滤等就可以完成绝大多数我们需要的查询了。但是,如果希望通过关键字的匹配来进行查询过滤,那么就需要基于相似度的查询,而不是原来的精确数值比较,全文索引就是为这种场景设计的。
25666 0
阿里云云原生一体化数仓正式发布  助力企业数据驱动业务创新
云原生一体化数仓是集阿里云大数据产品MaxCompute、DataWorks、Hologres三种产品能力于一体的一站式大数据处理平台。核心是3个一体化和全链路数据治理能力,包括离线实时一体、湖仓一体、分析服务一体、全链路数据治理。
1596 0
全面公测|Grafana服务:一张图表胜过千行指标&日志
Grafana 帮助运维人员轻松处理各类运维过程中遇到的各类数据可视化与分析难题。目前阿里云 Grafana 服务全面免费公测,帮助企业轻松构建运维数据可视化平台,轻松实现数据驱动运维!
1141 0
一起学Golang系列(五)初次接触Go语言可能遇到的各种坑!
前面介绍了Go语言的基础语法,所谓磨刀不误砍柴工,希望大家还是能熟悉掌握这些基础知识,这样后面真正学起Go来才会得心应手。 作为初学者。Go语言的语法有些和java类似,但也有很多不一样的地方。刚开始都会遇到各种各样的坑。下面就来总结下学习go语言的过程中,遇到的各种坑。
1007 0
设备接入--海康摄像头SDK
springboot-对接海康摄像头,兼容window和Linux环境
1322 0
Flink SQL 在快手的扩展和实践
快手实时计算团队技术专家张静、张芒在 FFA 2021 的分享
1133 0
无影云桌面,企业与个人的应用神器
阿里云无影云桌面( Elastic Desktop Service)的原产品名为弹性云桌面,融合了无影产品技术后更名升级。它可以为您提供易用、安全、高效的云上桌面服务,帮助您快速构建、高效管理桌面办公环境,提供安全、灵活的办公体系。
276280 0
+关注
jeanron100
Oracle ACE,《Oracle DBA工作笔记》作者 现就职于国内某互联网公司,擅长数据管理,数据迁移,性能优化,目前专注于开源技术,运维自动化和性能优化。
1178
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载