物化视图怎么使用

要做报表，原始数据表是一张明细表，展现给用户的数据还需要汇总一下。想到了物化视图，物化视图可以根据日志FAST刷新，在创建的时候才知道不能用group by ，改成Complete刷新才可以，但是Complete刷新应该很耗费资源吧。其实就是想做一张中间汇总表，大家是怎么做的？

14 个解决方案

#1

用普通视图耗资源很大么

#2

楼主的想法没有问题。
类似的问题，我以前做过一次。
利用物化视图可以实现对明细表的分组合计，
然后将物化视图定义为快速刷新，
就可以实时的反应明细表里面插入或者更新的数据了。

但是没明白

引用 楼主 jin_ok 的回复:

在创建的时候才知道不能用group by

这句话是什么意思，是定义物化视图的时候报错了吗？

#3

引用 1 楼 wildwave 的回复:

用普通视图耗资源很大么

我猜是因为明细表数据量很大，每次合计起来花时间，客户不爽了

#4

引用 3 楼 u012806870 的回复:

Quote: 引用 1 楼 wildwave 的回复:

用普通视图耗资源很大么

我猜是因为明细表数据量很大，每次合计起来花时间，客户不爽了

原始表数据会越来越多，就是觉得每次从原始表中查询效率不高

#5

引用 2 楼 u012806870 的回复:

楼主的想法没有问题。
类似的问题，我以前做过一次。
利用物化视图可以实现对明细表的分组合计，
然后将物化视图定义为快速刷新，
就可以实时的反应明细表里面插入或者更新的数据了。

但是没明白

Quote: 引用 楼主 jin_ok 的回复:

在创建的时候才知道不能用group by

这句话是什么意思，是定义物化视图的时候报错了吗？

是在创建FAST刷新的物化视图的时候报的错，好像是查询语句里面不能用Group by

#6

贴出来表结构和物化视图的定义吧，这样也好方便帮你找问题

#7

引用 5 楼 jin_ok 的回复:

Quote: 引用 2 楼 u012806870 的回复:

楼主的想法没有问题。
类似的问题，我以前做过一次。
利用物化视图可以实现对明细表的分组合计，
然后将物化视图定义为快速刷新，
就可以实时的反应明细表里面插入或者更新的数据了。

但是没明白

Quote: 引用 楼主 jin_ok 的回复:

在创建的时候才知道不能用group by

这句话是什么意思，是定义物化视图的时候报错了吗？

是在创建FAST刷新的物化视图的时候报的错，好像是查询语句里面不能用Group by

贴出来表结构和物化视图的定义吧，这样也好方便帮你找问题

#8

表结构和创建过程如下：
-- Create table
create table TBL_CALL_REC
(
  seqnum   NUMBER(10) not null,
  row_date DATE,
  row_time NUMBER(10),
  acwtime  NUMBER(10)
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table TBL_CALL_REC
  add constraint PK_SEQNUM primary key (SEQNUM)
  using index 
  tablespace BOUSER
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
数据如下：

--创建视图日志
create materialized view log on tbl_call_rec with rowid;

--创建物化视图，在需要时fast刷新
create materialized view mv_call_rec refresh fast on demand 
as 
select period_time,sum(acwtime) from 
(
  SELECT DECODE(LENGTH(row_time),3,lpad(SUBSTR(row_time,1,1),2,'0')||':00--'||lpad(SUBSTR(row_time,1,1),2,'0')||':59',4,SUBSTR(row_time,1,2)||':00--'||SUBSTR(row_time,1,2)||':59') AS 
  period_time,acwtime  FROM cms_call_rec 
)a group by period_time

报错 ORA-32401:"BOUSER"."TBL_CALL_REC"上的实体化视图日志没有新值

#9

没有人进来吗，就是创建物化视图的时候报的错

#10

引用 9 楼 jin_ok 的回复:

没有人进来吗，就是创建物化视图的时候报的错

你的物理表是TBL_CALL_REC
物化视图并没有从这个物理表里面取数据，而是从cms_call_rec取的数据。
这里我假设你贴错代码了，是想从TBL_CALL_REC里面取数据的。

你报这个错误的原因是在创建日志标的时候指定的选项不对
在最后加上  INCLUDING NEW VALUES  试试看
意思是在物理表更新的时候，把更新前和更新后的数据保存到物化视图日志表里面
目的是为了只针对这一条数据做物化视图的更新，以达到快速更新

接着说，加上上面的选项后，还是会报错的，错误信息：ORA-12033
意思是你在定义物化视图日志的时候还没有指定需要保存的字段
这个需要（字段1，字段2，字段3）这种形式定义

具体代码如下

create materialized view log on tbl_call_rec 
with rowid ,SEQUENCE
(seqnum,row_date,row_time,acwtime ) 
INCLUDING NEW VALUES;

这样改过后，你的物化视图应该就可以编译成功了

#11

引用 9 楼 jin_ok 的回复:

没有人进来吗，就是创建物化视图的时候报的错

谢谢

#12

按照u012806870的方法，可以创建
在这里我想请教一下

create materialized view log on tbl_call_rec 
with rowid ,SEQUENCE
(seqnum,row_date,row_time,acwtime ) 
INCLUDING NEW VALUES;

括号里的seqnum,row_date,row_time,acwtime几个字段是需要在日志中保存的字段，可不可以这样理解，括号里的几个字段是分组的字段、汇总的字段；或者说日志里必须要出现分组、汇总用到的字段

另外，创建日志的时候，with rowid ,SEQUENCE是什么意思？

#13

引用

括号里的seqnum,row_date,row_time,acwtime几个字段是需要在日志中保存的字段，可不可以这样理解，括号里的几个字段是分组的字段、汇总的字段；或者说日志里必须要出现分组、汇总用到的字段

可以这样理解

引用

另外，创建日志的时候，with rowid ,SEQUENCE是什么意思？

我只知道是为了记录更新行的ROWID和更新顺序。
而且在物化视图里面如果使用了SUM等函数的时候是必须指定的。

还有一种情况就是物化视图定义成功以后，元表更新后不能反映到物化视图里面。
那就需要在物化视图的定义上面加count（*）和count（汇总字段）就可以了

#14

新手学习了～～