初试物化视图
视图 物化 初试
2023-09-14 08:57:30 时间
通常我们使用EXP/IMP，EXPDP/IMPDP 进行数据库的迁移，但是若对数据的一致性要求比较严格，要求生产库的停机时间最短，尽量的减少对应用业务的影响，因为使用逻辑迁移过程中应用会对数据库持续写入，在导出，再导入过程中源数据库和目标数据库会出现数据不一致的现象，可以通过物化视图也可以实现这一功能。
环境：windows 11g + linux 5 11g
oracl 为windows 环境的实例，rac1 为linux 的实例！
建立测试表。
yang@oracl create table yangtab (id number primary key ,name varchar2(15));
在测试表上建立物化日志。
yang@oracl create materialized view log on yangtab;
实体化视图日志已创建。
yang@oracl insert into yangtab
2 select rownum id,
3 dbms_random.string(l,15) name
4 from dual
5 connect by level = 1e6;
已创建1000000行。
yang@oracl commit;
提交完成。
yang@oracl host date
当前日期: 2011/03/19 周六
输入新日期: (年月日)
yang@rac1 host time
当前时间: 12:49:45.67
输入新时间:
yang@oracl select count(1) from yangtab;
COUNT(1)
----------
1000000
在目标库上创建基于源库yangtab 的物化视图，并设置每隔1分钟进行刷新一次。
yang@rac1 create materialized view yangtab_mv
2 refresh fast start for update with sysdate next sysdate +1/1440
3 with primary key as select * from yangtab@linkyang;
Materialized view created.
yang@rac1 select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;
JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT
---- -------- --------------------- ---------- ------------------------- ---------------- -------------
146 YANG Mar 19 2011 11:27:01 11:27:01 Mar 19 2011 11:28:01 11:28:01 sysdate +1/1440 dbms_refresh.refresh("YANG"."YANGTAB_MV");
yang@rac1 select * from tab;
TNAME TABTYPE CLUSTERID
---------------------- ------- ----------
V TABLE
YANGALL TABLE
YANGTAB_MV TABLE
在源库插入数据后，从目标库上查询基表的数据
yang@rac1 select count(1) from yangtab@linkyang;
COUNT(1)
----------
1000000
此时查询物化视图还没数据，刷新物化视图需要时间。
yang@rac1 select count(1) from yangtab_mv;
COUNT(1)
----------
0
等待一段时间后，可以查询到物化视图中有数据了。
yang@rac1 select count(1) from yangtab_mv;
COUNT(1)
----------
1000000
在生产环境中可能还有应用对yangtab 进行操作，在对基表进行最后一次刷新前，将应用迁移到目标库上对 新建的物化视图进行访问（物化视图必须是可更新的，否则在目标库不可对mv 进行dml操作）。
在目标库中手工执行刷新物化视图。
yang@rac1 EXEC DBMS_MVIEW.REFRESH(MV_YANGTAB);
PL/SQL 过程已成功完成。
此操作保证目标和源数据库中的数据一致。至此 从windows 环境下将数据迁移到linux环境的操作完成。
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过执行计划详解”行转列”,”列转行”是如何实现的 本篇文章讲解的主要内容是：***目前Oracle支持的行列互换有两种方式：case when、pivot\unpivot,我将通过几个案例来给大家详解如何通过这两种方式实现“行转列”，“列转行”的需求，并通过执行计划看case when、pivot\unpivot二者的底层逻辑关系以及效率上的影响。***
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式 怎样对SQL查询结果集分页比较好、平时你用分析函数优化传统查询，所以你会不会认为分析函数一定比传统查询效率高？一个实验告诉你答案、我想对数据进行隔行抽样应该怎么实现？【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。分析查询的一个小建议，可能大家平时为了方便，用row_number做分页的比较多，但是在有些场景，这个效率真的挺低。
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起 本篇文章讲解的主要内容是：***如果有重复数据如何检查出两个表中的差异数据及对应条数、表连接做聚合容易出现重复计算的错误、多表查询空值处理问题、NOT IN的子查询范围不能是空值，否则查询结果为空。***
开发踩坑记录之三：PostgreSQL数据库表唯一性约束失效 在设计数据库表过程中，我们通常会对数据库表进行唯一性约束，以防止事务不一致导致的相同数据的重复插入问题。但是在实际开发中发现，即使设置了数据库表的唯一性约束，仍然出现了相同数据重复插入的问题。
环境：windows 11g + linux 5 11g
oracl 为windows 环境的实例，rac1 为linux 的实例！
建立测试表。
yang@oracl create table yangtab (id number primary key ,name varchar2(15));
在测试表上建立物化日志。
yang@oracl create materialized view log on yangtab;
实体化视图日志已创建。
yang@oracl insert into yangtab
2 select rownum id,
3 dbms_random.string(l,15) name
4 from dual
5 connect by level = 1e6;
已创建1000000行。
yang@oracl commit;
提交完成。
yang@oracl host date
当前日期: 2011/03/19 周六
输入新日期: (年月日)
yang@rac1 host time
当前时间: 12:49:45.67
输入新时间:
yang@oracl select count(1) from yangtab;
COUNT(1)
----------
1000000
在目标库上创建基于源库yangtab 的物化视图，并设置每隔1分钟进行刷新一次。
yang@rac1 create materialized view yangtab_mv
2 refresh fast start for update with sysdate next sysdate +1/1440
3 with primary key as select * from yangtab@linkyang;
Materialized view created.
yang@rac1 select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;
JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT
---- -------- --------------------- ---------- ------------------------- ---------------- -------------
146 YANG Mar 19 2011 11:27:01 11:27:01 Mar 19 2011 11:28:01 11:28:01 sysdate +1/1440 dbms_refresh.refresh("YANG"."YANGTAB_MV");
yang@rac1 select * from tab;
TNAME TABTYPE CLUSTERID
---------------------- ------- ----------
V TABLE
YANGALL TABLE
YANGTAB_MV TABLE
在源库插入数据后，从目标库上查询基表的数据
yang@rac1 select count(1) from yangtab@linkyang;
COUNT(1)
----------
1000000
此时查询物化视图还没数据，刷新物化视图需要时间。
yang@rac1 select count(1) from yangtab_mv;
COUNT(1)
----------
0
等待一段时间后，可以查询到物化视图中有数据了。
yang@rac1 select count(1) from yangtab_mv;
COUNT(1)
----------
1000000
在生产环境中可能还有应用对yangtab 进行操作，在对基表进行最后一次刷新前，将应用迁移到目标库上对 新建的物化视图进行访问（物化视图必须是可更新的，否则在目标库不可对mv 进行dml操作）。
在目标库中手工执行刷新物化视图。
yang@rac1 EXEC DBMS_MVIEW.REFRESH(MV_YANGTAB);
PL/SQL 过程已成功完成。
此操作保证目标和源数据库中的数据一致。至此 从windows 环境下将数据迁移到linux环境的操作完成。
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过执行计划详解”行转列”,”列转行”是如何实现的 本篇文章讲解的主要内容是：***目前Oracle支持的行列互换有两种方式：case when、pivot\unpivot,我将通过几个案例来给大家详解如何通过这两种方式实现“行转列”，“列转行”的需求，并通过执行计划看case when、pivot\unpivot二者的底层逻辑关系以及效率上的影响。***
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式 怎样对SQL查询结果集分页比较好、平时你用分析函数优化传统查询，所以你会不会认为分析函数一定比传统查询效率高？一个实验告诉你答案、我想对数据进行隔行抽样应该怎么实现？【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。分析查询的一个小建议，可能大家平时为了方便，用row_number做分页的比较多，但是在有些场景，这个效率真的挺低。
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起 本篇文章讲解的主要内容是：***如果有重复数据如何检查出两个表中的差异数据及对应条数、表连接做聚合容易出现重复计算的错误、多表查询空值处理问题、NOT IN的子查询范围不能是空值，否则查询结果为空。***
开发踩坑记录之三：PostgreSQL数据库表唯一性约束失效 在设计数据库表过程中，我们通常会对数据库表进行唯一性约束，以防止事务不一致导致的相同数据的重复插入问题。但是在实际开发中发现，即使设置了数据库表的唯一性约束，仍然出现了相同数据重复插入的问题。