ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一个物化视图快速刷新的问题

一个物化视图快速刷新的问题

原创 Linux操作系统 作者:yangtingkun 时间:2007-12-04 00:00:00 0 删除 编辑

今天帮人解决了一个物化视图快速刷新的问题,简单记录一下解决方法。


问题倒是不复杂,就是一个普通的聚集物化视图的快速刷新问题,下面通过一个例子模拟一下:

SQL> CREATE TABLE T AS SELECT * FROM DBA_FREE_SPACE;

表已创建。

SQL> CREATE MATERIALIZED VIEW LOG ON T WITH ROWID, SEQUENCE (TABLESPACE_NAME, BLOCKS, BYTES)
2 INCLUDING NEW VALUES;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
2 SELECT TABLESPACE_NAME,
3 COUNT(*) CNT,
4 COUNT(BLOCKS) BLOCK_CNT,
5 COUNT(BYTES) BYTES_CNT,
6 AVG(BLOCKS) BLOCK_AVG,
7 AVG(BYTES) BYTES_AVG
8 FROM T
9 GROUP BY TABLESPACE_NAME;

实体化视图已创建。

SQL> SELECT * FROM MV_T;

TABLESPACE_NAME CNT BLOCK_CNT BYTES_CNT BLOCK_AVG BYTES_AVG
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSAUX 177 177 177 54.2824859 444682.124
UNDOTBS1 3 3 3 9674.66667 79254869.3
YANGTK 3 3 3 93.3333333 764586.667
USERS 1 1 1 2552 20905984
SYSTEM 2 2 2 432 3538944

SQL> DESC MV_T
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------
TABLESPACE_NAME VARCHAR2(30)
CNT NUMBER
BLOCK_CNT NUMBER
BYTES_CNT NUMBER
BLOCK_AVG NUMBER
BYTES_AVG NUMBER

就是这样一个简单的例子。本来这个例子并没有什么,物化视图也是可以快速刷新的,问题是由于出现了AVG函数,造成最终结果出现了小数。

一方面是由于查询结果不关系小数部分,只是打算显示整数部分,另一方面考虑,如果物化视图的数据量很大,存储小数部分会占用较多的空间。

出于这两点考虑,这个物化视图被改写为:

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
2 SELECT TABLESPACE_NAME,
3 COUNT(*) CNT,
4 COUNT(BLOCKS) BLOCK_CNT,
5 COUNT(BYTES) BYTES_CNT,
6 ROUND(AVG(BLOCKS)) BLOCK_AVG,
7 ROUND(AVG(BYTES)) BYTES_AVG
8 FROM T
9 GROUP BY TABLESPACE_NAME;
FROM T
*
8 行出现错误:
ORA-12015:
不能从复杂查询中创建一个可快速刷新的实体化视图

这就是前面所说的快速刷新的问题。

其实问题并不难确定,肯定是由于ROUND函数造成的,下面通过DBMS_MVIEW.EXPLAIN_MVIEW来验证这一点:

SQL> @?/rdbms/admin/utlxmv

Table created.

创建MV_CAPABILITIES_TABLE表后,检查导致无法快速刷新的原因:

SQL> BEGIN
2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT TABLESPACE_NAME,
3 COUNT(*) CNT,
4 COUNT(BLOCKS) BLOCK_CNT,
5 COUNT(BYTES) BYTES_CNT,
6 ROUND(AVG(BLOCKS)) BLOCK_AVG,
7 ROUND(AVG(BYTES)) BYTES_AVG
8 FROM T
9 GROUP BY TABLESPACE_NAME');
10 END;
11 /

PL/SQL 过程已成功完成。

SQL> SELECT CAPABILITY_NAME, POSSIBLE, MSGTXT
2 FROM MV_CAPABILITIES_TABLE
3 WHERE CAPABILITY_NAME LIKE 'REFRESH%';

CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------------------------------
REFRESH_COMPLETE Y
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
表达式中聚集函数嵌套
REFRESH_FAST_AFTER_ONETAB_DML N
查看禁用 REFRESH_FAST_AFTER_INSERT 的原因
REFRESH_FAST_AFTER_ANY_DML N
查看禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因
REFRESH_FAST_PCT N PCT
不可能在实体化视图中的任何从表上

已选择6行。

现在错误一目了然了,就是由于ROUND函数造成的。解决这个问题的唯一方法就是去掉聚集函数上嵌套的函数。

其实对于添加ROUND的第一个目的很容易实现,只需要在物化视图上面封装一层VIEW,在VIEW的查询中指定ROUND函数。

解决第二个目的,即避免小数部分占用过多的空间,其实也是有办法实现的,而且也不会影响物化视图的快速刷新,只不过这个方法不是直接实现的,而是采用了变通的方法:建立物化视图的基表,限制相应列的精度,然后在这个表上创建ON PREBUILT TABLE物化视图,且降低物化视图字段的精度,采用这种方法,即达到了ROUND函数的目的,又不会影响物化视图的快速刷新。下面看具体的例子:

SQL> CREATE TABLE MV_T
2 (TABLESPACE_NAME VARCHAR2(30),
3 CNT NUMBER,
4 BLOCK_CNT NUMBER,
5 BYTES_CNT NUMBER,
6 BLOCK_AVG NUMBER(38, 0),
7 BYTES_AVG NUMBER(38, 0));

表已创建。

SQL> CREATE MATERIALIZED VIEW MV_T ON PREBUILT TABLE WITH REDUCED PRECISION
2 REFRESH FAST AS
3 SELECT TABLESPACE_NAME,
4 COUNT(*) CNT,
5 COUNT(BLOCKS) BLOCK_CNT,
6 COUNT(BYTES) BYTES_CNT,
7 AVG(BLOCKS) BLOCK_AVG,
8 AVG(BYTES) BYTES_AVG
9 FROM T
10 GROUP BY TABLESPACE_NAME;

实体化视图已创建。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T', 'C')

PL/SQL 过程已成功完成。

SQL> SELECT * FROM MV_T;

TABLESPACE_NAME CNT BLOCK_CNT BYTES_CNT BLOCK_AVG BYTES_AVG
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSAUX 177 177 177 54 444682
UNDOTBS1 3 3 3 9675 79254869
YANGTK 3 3 3 93 764587
USERS 1 1 1 2552 20905984
SYSTEM 2 2 2 432 3538944

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69528/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ORA-600(925)错误
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10480785