oracle 创建可更新视图,ORA-12015 不能从复杂查询中创建一个可快速刷新的实体化视图 问题解决咨询...

各位朋友，

之前有提一个问题：http://www.itpub.net/thread-2070680-1-1.html

在各位热心的朋友和版主的帮助下，解决了生成统计表的功能。

现在的问题是源数据库不断更新，我希望能够利用日志来做个定时刷新，我仿照版主@

问题5.png (6.48 KB, 下载次数: 27)

2016-11-2 15:19 上传

以下是所有的过程：

--源数据表

CREATE TABLE TEST (ID VARCHAR2(32),DATEs DATE,a NUMBER);

--插入源数据

insert into test (ID, DATES, A) values ('50201281', to_date('01-01-2015', 'dd-mm-yyyy'), 1);

insert into test (ID, DATES, A)values ('50201281', to_date('02-01-2015', 'dd-mm-yyyy'), 2);

insert into test (ID, DATES, A)values ('50201281', to_date('03-01-2015', 'dd-mm-yyyy'), 3);

insert into test (ID, DATES, A)values ('50201281', to_date('01-01-2016', 'dd-mm-yyyy'), 4);

insert into test (ID, DATES, A)values ('50201281', to_date('02-01-2016', 'dd-mm-yyyy'), 5);

insert into test (ID, DATES, A)values ('50201281', to_date('03-01-2016', 'dd-mm-yyyy'), 6);

insert into test (ID, DATES, A)values ('50201281', to_date('01-01-2017', 'dd-mm-yyyy'), 7);

insert into test (ID, DATES, A)values ('50201281', to_date('02-01-2017', 'dd-mm-yyyy'), 8);

insert into test (ID, DATES, A)values ('50201281', to_date('03-01-2017', 'dd-mm-yyyy'), 9);

insert into test (ID, DATES, A)values ('50201282', to_date('01-01-2011', 'dd-mm-yyyy'), 10);

insert into test (ID, DATES, A)values ('50201282', to_date('02-01-2011', 'dd-mm-yyyy'), 11);

insert into test (ID, DATES, A)values ('50201282', to_date('03-01-2011', 'dd-mm-yyyy'), 12);

insert into test (ID, DATES, A)values ('50201282', to_date('01-01-2016', 'dd-mm-yyyy'), 13);

insert into test (ID, DATES, A)values ('50201282', to_date('02-01-2016', 'dd-mm-yyyy'), 14);

insert into test (ID, DATES, A)values ('50201282', to_date('03-01-2016', 'dd-mm-yyyy'), 15);

insert into test (ID, DATES, A)values ('50201282', to_date('01-01-2017', 'dd-mm-yyyy'), 16);

insert into test (ID, DATES, A)values ('50201282', to_date('02-01-2017', 'dd-mm-yyyy'), 17);

insert into test (ID, DATES, A)values ('50201282', to_date('03-01-2017', 'dd-mm-yyyy'), 18);

新建主键：

alter table test add constraint test_pk primary key (ID，DATES);

建立源数据表日志：

CREATE MATERIALIZED VIEW LOG ON test WITH SEQUENCE, ROWID(a),PRIMARY KEY INCLUDING NEW VALUES;

建立统计表：

CREATE TABLE TEST01 AS

SELECT ID ID ,

to_char(DATES, 'mm') month,

to_char(DATES, 'dd') day,

trunc((sum(A)/count(distinct to_char(DATES, 'yyyy'))),2)  MYAVG,

MIN (to_char(DATES, 'YYYY')) BGYR,

MAX (to_char(DATES, 'YYYY')) EDYR,

MAX (to_char(DATES, 'YYYY')) - MIN (to_char(DATES, 'YYYY'))+1 GAP,

COUNT(A) cnt_drp,

count(*) cnt,

CAST( ' null' AS VARCHAR2(100)) COMMENTS,

sysdate MODITIME

FROM TEST

GROUP BY ID, to_char(DATES, 'mm'), to_char(DATES, 'dd');

建立物化视图：

create materialized view test01 on prebuilt table

REFRESH FAST

START WITH SYSDATE+ 10/(24*60) NEXT  SYSDATE + 10/(24*60)  WITH ROWID

AS

SELECT ID ID ,

to_char(DATES, 'mm') month,

to_char(DATES, 'dd') day,

trunc((sum(A)/count(distinct to_char(DATES, 'yyyy'))),2)  MYAVG,

MIN (to_char(DATES, 'YYYY')) BGYR,

MAX (to_char(DATES, 'YYYY')) EDYR,

MAX (to_char(DATES, 'YYYY')) - MIN (to_char(DATES, 'YYYY'))+1 GAP,

COUNT(A) cnt_drp,

count(*) cnt,

CAST( ' null' AS VARCHAR2(100)) COMMENTS,

sysdate MODITIME

FROM TEST

GROUP BY ID, to_char(DATES, 'mm'), to_char(DATES, 'dd');

这个时候就报错了，请问下有没有办法解决这个报错？