利用Oracle物化视图优化项目查询

最新推荐文章于 2024-07-24 06:39:49 发布

it夜猫who

最新推荐文章于 2024-07-24 06:39:49 发布

阅读量2k

点赞数

分类专栏： java oracle 文章标签：数据库 oracle 应用程序性能优化

本文链接：https://blog.youkuaiyun.com/YSOLA4/article/details/38319641

版权

java 同时被 2 个专栏收录

18 篇文章

订阅专栏

oracle

1 篇文章

订阅专栏

Oracle物理化视图小结

以下纯粹为本人在优化个人项目中的查询较慢时的一些小心得小总结,仅供参考,由于知识水平有限,欢迎提出修正意见

1简介

物化视图，它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

物化视图在OLAP(联机分析处理系统On-LineAnalytical Processing)中应用非常普遍。

下面稍微罗列一下物化视图的适用场景或条件

1 专门应用于查询使用。

2 对数据实时性要求不是非常严格（定时更新的物化视图会出现数据不一致的情况）。

3 查询中的sql语句或视图中包含了很多的表关联，且各数据表的数据量较大（>1万）会产生一个较大的集合（如笛卡尔乘积）运算，

4一些较为费时的数据库聚合运算，group by运算等

5 在使用远程数据库的时候作为本地数据副本使用

等等

2 创建

关于Oracle物化视图的创建网上也有很多资料，可自行搜索，有些文章介绍得比较详细的，如：

http://blog.youkuaiyun.com/suncrafted/article/details/4300358

http://www.itpub.net/thread-1614812-1-1.html

在这里提点值得注意的地方，当创建 On Commit物化视图， refresh fast on commit 的时候，视图查询语句中的表RowId需要出现在查询语句中并且需要针对表的rowid进行日志记录。同时，物化视图还能添加索引，可针对实际情况进行添加，进一步提升查询速度。

----rowid日志

CREATE MATERIALIZED VIEW LOG ONDIRTREE WITH ROWID;

CREATE MATERIALIZED VIEW LOG ONDIR_TREE_INFOCLASS_MIDTABLE WITH ROWID;

CREATE MATERIALIZED VIEW LOG ONDIRINFOCLASS WITH ROWID;

CREATE MATERIALIZED VIEW LOG ONDIR_DATAITEM_MIDDLE_TABLE WITH ROWID;

CREATE MATERIALIZED VIEW LOG ONDIRDATAITEM WITH ROWID;

----创建视图，需要把所有涉及到的表的rowid带上

create materializedview V_M_DIRTREE_SUBJ_ITEM

refresh

fast

on commit

with rowid as

SELECT A.RIDVID,A.RID,A.NODENAME,A.PRID,'1'ISTREENODE,A.NODECODE,A.REMARK,A.REFDIMEMSION,A.ROWID ARD,A.ROWID BRD,A.ROWIDCRD

FROM DIRTREE A

UNION ALL

SELECT D.RID VID,D.RID,B.NODENAME,D.DIRECTIORRIDPRID,'0' ISTREENODE,'','','',B.ROWID BRD,C.ROWID CRD,D.ROWID DRD

FROMCFG.DIRINFOCLASS B,DIRTREE C,

DIR_TREE_INFOCLASS_MIDTABLE D

WHERE B.RID = D.INFOCLASSRID AND C.RID =D.DIRECTIORRID

UNION ALL

SELECT A.RIDVID,A.RID,C.DATAITEMNAME,B.RID PRID,'-1' ISTREENODE,'','','',A.ROWIDARD,B.ROWID BRD,C.ROWID CRD

FROMCFG.DIR_DATAITEM_MIDDLE_TABLE A

,CFG.DIR_TREE_INFOCLASS_MIDTABLE B

,CFG.DIRDATAITEM C

WHERE A.DIRECTORYRID = B.DIRECTIORRID ANDA.DIRINFOCLASSRID = B.INFOCLASSRID

AND A.DATAITEMRID = C.RID;

----索引

/*==============================================================*/

/* Index:V_M_DIRTREE_SUBJ_ITEM_RID_IDX */

/*==============================================================*/

create indexV_M_DIRTREE_SUBJ_ITEM_RID_IDX on V_M_DIRTREE_SUBJ_ITEM (

RID ASC

);

/*==============================================================*/

/* Index:V_M_DIRTREE_SUBJ_ITEM_PRID_IDX */

/*==============================================================*/

create indexV_M_DIRTREE_SUBJ_ITEM_PRID_IDX on V_M_DIRTREE_SUBJ_ITEM (

PRID ASC

);

另外，如果在查询语句中出现groupby，需要把分组中用到的字段在查询块里用count(col1) 形式出现，举例如目录知识库的其中一个物化视图：

drop materialized view V_M_DIR_MID_PROP;

/*==============================================================*/

/* View: V_M_DIR_MID_PROP */

/*==============================================================*/

create materialized viewV_M_DIR_MID_PROP

refresh

force

on commit

with primary key as

SELECTA.DIRECTORYRID,A.DIRINFOCLASSRID,

MAX(decode(B.PROPNAME, '共享类型', A.PROPVAL, NULL)) AS GXLX,

MAX(decode(B.PROPNAME, '公开类型', A.PROPVAL, NULL)) AS GKLX,

----要在这里以count的形式对分组字段进行count添加

COUNT(DIRECTORYRID)DIRCNT,COUNT(DIRINFOCLASSRID) DRICNT

FROM CFG.DIR_PROP_MIDDLE_TABLE A

JOIN DIRPROP B ON B.RID =A.PROPRID

WHERE B.PROPNAME IN('共享类型','公开类型')

如果查询语句中出现

嵌套子表时，可先把嵌套子表生成为按照On commit的物化视图，再把查询语句替换为已生成的物化视图，如：

3 性能测试,网上资料很多,可自行搜索 oracle的sql性能调试

参考网上资料:http://blog.youkuaiyun.com/tianlesoftware/article/details/5827245

以目录知识库为例，一个包含了树形结构查询的数据表由普通视图改为了物化视图后的性能变化，以oracle的explan plan为准

调整前：

调整后：

可看到，在合适的场景中，物化视图会大大的减少数据库查询所消耗的资源，包括CPU以及IO，从而实现查询优化的目的。

4 缺点

物化视图的缺点也是显而易见的，

1 按照命令或定时更新的物化视图，会有一定的数据延迟（与真实表数据不一致）。

2 按基表提交而更新的物化视图，会在基表数据发生变化的时候造成数据库额外的资源消耗，降低基表的修改效率。

3 物化视图会直接占用物理储存空间，是一个用物理空间换时间的策略。不过在当今储存成本越来越低的今天，这个反而已经不成为了问题。