求助：物化视图问题

显示全部楼层 · 2007-9-26 17:05:46

各位大侠，我新建了一个原始表TABLE_TEST：
create table TABLE_TEST(time varchar2(50),category varchar2(50),cvalue varchar2(50));
然后插入测试数据：
insert into TABLE_TEST values('2006-1-1','A','500');
insert into TABLE_TEST values('2006-1-1','B','550');
insert into TABLE_TEST values('2006-1-1','C','750');
insert into TABLE_TEST values('2006-1-2','A','500');
insert into TABLE_TEST values('2006-1-2','B','580');
insert into TABLE_TEST values('2006-1-2','C','800');
insert into TABLE_TEST values('2006-1-3','A','560');
insert into TABLE_TEST values('2006-1-3','B','560');
insert into TABLE_TEST values('2006-1-3','C','700');
insert into TABLE_TEST values('2006-1-4','B','880');
insert into TABLE_TEST values('2006-1-4','C','990');
select * from table_test;
TIME CATEGORY CVALUE
----------------------------
2006-1-1 A 500
2006-1-1 B 550
2006-1-1 C 750
2006-1-2 A 500
2006-1-2 B 580
2006-1-2 C 800
2006-1-3 A 560
2006-1-3 B 560
2006-1-3 C 700
2006-1-4 B 880
2006-1-4 C 990
这时，我建立了一个行列转换的物化视图供用户查询：
create materialized view mv_table_test
build immediate
refresh complete
as
select time,
max(decode(category, 'A', CVALUE, null)) A,
max(decode(category, 'B', CVALUE, null)) B,
max(decode(category, 'C', CVALUE, null)) C
from table_test
group by time
order by time asc
select * from mv_table_test;
TIME A B C
-----------------------------------
2006-1-1 500 550 750
2006-1-2 500 580 800
2006-1-3 560 560 700
2006-1-4 880 990
此时，用户需要将2006-1-4号C的值由990改为1000，由于物化视图是直读的，所以我只能更改原始表，然后刷新物化视图。但由于物化视图是完全刷新，且TABLE_TEST表有上百万条记录，每次当用户更改某一个值后如果都要刷新一遍物化视图的话，用户会觉得等待时间太长了，我想请问一下有没有什么好的方法可以解决这个效率问题？或是有执行效率更高的行列转换SQL也行，先谢谢大家了。

千问 · 2007-9-26 17:05:46

如果可以给TABLE_TEST加上主键的话，那你可以用物化试图的快速刷新，即：fresh fast！快速刷新读log字段，不全表扫描，这是最好的办法！
如果不能给TABLE_TEST加主键的话，建议用trigger!

千问 · 2007-9-26 17:05:46

如果是加主键的话后面怎么写啊？
可不可以针对我的例子帮我把后面的实现写出来啊？
谢谢。

千问 · 2007-9-26 17:05:46

即我为表增加主键：
alter table TABLE_TEST add primary key(time, category);
然后建立物化视图日志(该日志表包含原始表的所有字段)：
create materialized view log on TABLE_TEST with primary key(cvalue);
然后再重新建立fast刷新的物化视图：
create materialized view mv_table_test
build immediate
refresh fast
as
select time,
max(decode(category, 'A', CVALUE, null)) A,
max(decode(category, 'B', CVALUE, null)) B,
max(decode(category, 'C', CVALUE, null)) C
from table_test
group by time
order by time asc
结果oracle报：
ORA-12032：cannot use rowid column from materialized view log on "CURDBUSER"."TABLE_TEST"
错误，请高手们指教，谢谢。

千问 · 2007-9-26 17:05:46

什么叫物化视图？

千问 · 2007-9-26 17:05:46

create materialized view mv_table_test

REFRESHSTART WITH SYSDATE

NEXTsysdate

WITH PRIMARY KEY
as
select time,
max(decode(category, 'A', CVALUE, null)) A,
max(decode(category, 'B', CVALUE, null)) B,
max(decode(category, 'C', CVALUE, null)) C
from table_test
group by time
order by time asc;

千问 · 2007-9-26 17:05:46

建立快速刷新的物化试图需要符合很多要求的！
首先：
所有类型的快速刷新物化视图都必须满足的条件：
1.物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM；
2.物化视图不能包含对LONG和LONG RAW数据类型的引用。
其次包含聚集的物化视图（就是你的物化试图）需要满足的条件：
1.必须满足所有快速刷新物化视图都满足的条件；
2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：
(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；
(2)必须指明ROWID和INCLUDING NEW VALUES；
(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。
3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；
4.必须指定COUNT(*)；
5.如果指明了除COUNT之外的聚集函数，则COUNT(expr)也必须存在；
比如：包含SUM(a)，则必须同时包含COUNT(a)。
6.如果指明了VARIANCE(expr)或STDDEV(expr)，除了COUNT(expr)外，SUM(expr)也必须指明；
Oracle推荐同时包括SUM(expr*expr)。
7.SELECT列表中必须包括所有的GROUP BY列；
8.当物化视图属于下面的某种情况，则快速刷新只支持常规DML插入和直接装载，这种类型的物化视图又称为INSERT-ONLY物化视图；
物化视图包含MIN或MAX聚集函数；
物化视图包含SUM(expr)，但是没有包括COUNT(expr)；
物化视图没有包含COUNT(*)。
注意：如果建立了这种物化视图且刷新机制是ON COMMIT的，则会存在潜在的问题。当出现了UPDATE或DELETE语句，除非手工完全刷新解决这个问题，否则物化视图至此以后都不再自动刷新，且不会报任何错误。
9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；
10.如果物化视图建立在视图或子查询上，则要求视图必须可以完全合并的。
11.如果没有外连接，则对WHERE语句没有限制。如果包含外连接，则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图，快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。
12.对于包含了ROLLUP、CUBE、GROUPING SET的物化视图必须满足下列限制条件：
SELECT语句列表中应该包含GROUPING标识符：可以是GROUP BY表达式中所有列的GROUPING_ID函数，也可以是GROUP BY表达式中每一列的GROUPING函数；
例如：GROUP BY语句为：GROUP BY CUBE(a, b)，则SELECT列表应该包括GROUPING_ID(a, b)或者GROUPING(a)和GROUPING(b)。
GROUP BY不能产生重复的GROUPING。
比如：GROUP BY a, ROLLUP(a, b)则不支持快速刷新，因为包含了重复的GROUPING：(a), (a, b), (a)。

千问 · 2007-9-26 17:05:46

那我想请问一下高手们没有办法让物化视图刷新快起来呢？

千问 · 2007-9-26 17:05:46

最初由 myfriend2010 发布
[B]建立快速刷新的物化试图需要符合很多要求的！
首先：
所有类型的快速刷新物化视图都必须满足的条件：
1.物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM；
2.物化视图不能包含对LONG和LONG RAW数据类型的引用。
其次包含聚集的物化视图（就是你的物化试图）需要满足的条件：
1.必须满足所有快速刷新物化视图都满足的条件；
2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：
(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；
(2)必须指明ROWID和INCLUDING NEW VALUES；
(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。
3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；
4.必须指定COUNT(*)；
5.如果指明了除COUNT之外的聚集函数，则COUNT(expr)也必须存在；
比如：包含SUM(a)，则必须同时包含COUNT(a)。
6.如果指明了VARIANCE(expr)或STDDEV(expr)，除了COUNT(expr)外，SUM(expr)也必须指明；
Oracle推荐同时包括SUM(expr*expr)。
7.SELECT列表中必须包括所有的GROUP BY列；
8.当物化视图属于下面的某种情况，则快速刷新只支持常规DML插入和直接装载，这种类型的物化视图又称为INSERT-ONLY物化视图；
物化视图包含MIN或MAX聚集函数；
物化视图包含SUM(expr)，但是没有包括COUNT(expr)；
物化视图没有包含COUNT(*)。
注意：如果建立了这种物化视图且刷新机制是ON COMMIT的，则会存在潜在的问题。当出现了UPDATE或DELETE语句，除非手工完全刷新解决这个问题，否则物化视图至此以后都不再自动刷新，且不会报任何错误。
9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；
10.如果物化视图建立在视图或子查询上，则要求视图必须可以完全合并的。
11.如果没有外连接，则对WHERE语句没有限制。如果包含外连接，则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图，快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。
12.对于包含了ROLLUP、CUBE、GROUPING SET的物化视图必须满足下列限制条件：
SELECT语句列表中应该包含GROUPING标识符：可以是GROUP BY表达式中所有列的GROUPING_ID函数，也可以是GROUP BY表达式中每一列的GROUPING函数；
例如：GROUP BY语句为：GROUP BY CUBE(a, b)，则SELECT列表应该包括GROUPING_ID(a, b)或者GROUPING(a)和GROUPING(b)。
GROUP BY不能产生重复的GROUPING。
比如：GROUP BY a, ROLLUP(a, b)则不支持快速刷新，因为包含了重复的GROUPING：(a), (a, b), (a)。 [/B]
似乎是出自我的文章

千问 · 2007-9-26 17:05:46

最初由 huanghaozzz 发布
[B]那我想请问一下高手们没有办法让物化视图刷新快起来呢？ [/B]
你的这个物化视图没有办法进行快速刷新。
如果完全刷新是可以接受的，那么设置刷新的原子性为FALSE
采用TRUNCATE + INSERT的方式要比DELETE + INSERT 快一些