Oracle物化视图

问题背景

客户提出：根据券号来查询相关券活动的信息，提供 REST 接口供查询;由于相关券活动是由第三方生成,下发到我们系统,再由我们上传回客户,这中间那个环节出现问题不清楚,等到发现问题再处理,客户项目上需求已经来不及;故此,客户希望我们系统提供相关接口供查询相关券活动生成和推送数据

–根据券号
查询
–券号，模板，定义数量，已收到数据路，已推送数量

问题：所查询表数据量非常巨大,响应性能必须解决

处理方法

因为 couponactivity 券活动表数据量非常大已经达到数千万数量，为提高效应效率；采取将相关数据汇总到物化视图，接口通过查询视图来返回相关数据信息的策略

创建物化视图日志

物化视图日志是很容易建成功的 , 但问题是日志是能建成功可物化视图总会报出各种问题;经过反复处理报错的问题查找相关资料;
主要受益于浅析为何Oracle物化视图对distinct, group by不支持快速刷新、 oracle物化视图日志结构和 Oracle物化视图、
oracle中的decode的使用

创建物化视图

首先写出符合预期结果的 sql 语句,然后以创建增量方式快速刷新的物化视图为目标处理解决过程中遇到的问题

最终实现的如下物化视图

--创建物化视图日志
create materialized view log on couponactivity with primary key,sequence,rowid(projectcode,title,codeqty) including new values;
create materialized view log on coupon with primary key,sequence,rowid(couponactivityid,o2ostate) including new values;
--创建物化视图，该物化视图指定了自创建之后开始每10分钟快速增量刷新一次，以保证物化视图的数据和原表统一
create materialized view MVIEW_couponActivityCountData
Build immediate refresh fast on demand  with rowid start with sysdate next sysdate + 10/(24*60) as 
select substr(a.projectcode,
              0,
              decode(sign(instr(a.projectcode, ',', -1, 1) - 1),
                     -1,
                     length(a.projectcode),
                     1,
                     instr(a.projectcode, ',', -1, 1) - 1)) billno,
       a.projectcode projectcode,
       a.title activityname,
       a.active_id activityId,
       a.codeqty codeqty,
       count(p.couponactivityid) receiveqty,
       count(case
               when p.o2ostate = 1 then
                1
               else
                null
             end) pushedqty，
       count(*)
  from coupon p, couponactivity a
 where a.active_id = p.couponactivityid
   and a.projectcode is not null
group by a.projectcode, a.title, a.active_id, a.codeqty;

创建物化视图注意点

1.创建适当的物化视图日志，是实现快速增量刷新的物化视图的前提
2.查询中需要添加 count(*) 才能实现定时自动刷新
下述是试验 sql 语句

-----------------------------------------------------------------------------------------------------
--   发现一定要加一个count(*)才能自动刷新,定时刷新
-----------------------------------------------------------------------------------------------------
--create materialized view log on coupon with primary key,sequence,rowid(couponactivityid,o2ostate) including new values;
--  with rowid start sysdate next sysdate + 1/(24*60)
--  next null
/*create materialized view MVIEW_couponCountData
Build immediate refresh fast on demand  with rowid start with sysdate next sysdate + 1/(24*60) as 
select p.couponactivityid activityId,
       count(p.couponactivityid) receiveqty,
       count(case
               when p.o2ostate = 1 then
                1
               else
                null
             end) pushedqty,
       count(*) 
  from coupon p
  group by p.couponactivityid;
*/
--drop materialized view log on coupon;
--drop materialized view MVIEW_couponCountData;
select * from mlog$_coupon;
select * from coupon p where p.couponactivityid='81391';
/*update coupon p set p.o2ostate=0 where p.couponactivityid='81391';
commit;*/

扩展关于 DBLink 跨库查询相关

--查看 dblink信息
--select * from ALL_DB_LINKS;

--选择一个DBLink后，要关联这个DBLink下所有的表名
--select table_name from user_tables@crmquery;

总结

主要耗时是：在创建了物化视图日志之后,创建物化视图时报各种问题;查找很多资料解决相关问题,原因就是创建的物化视图不正确,对于这种复杂查询的 sql 查询创建的物化视图,需要创建 primary key sequence rowid 的物化视图日志,
其中 rowid 中的列由 group by 语句后面的相关字段有关;另外就是对于查询结构的要求,对字符串的截取 substr 和 case 语句的写法（类似程序的 if else 功能）使用和学习