linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

Oracle复杂视图优化案例分析

时间:2017-05-16  来源:未知  作者:linuxsir首页

在使用Oracle数据库过程中,视图是作为数据库对象存在的,因而,在创建了这样的视图后,就可以通过工具或者数据字典来查看视图的相关信息。视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义在基表上的触发器。

Oracle的视图分为简单视图和复杂视图两种,简单视图就是从单一的表中获取数据,其中不包含函数和数据组等,复杂视图是指在视图定义中包括了聚合函数、分析函数、用户自定义函数、多表关联、子查询等。此类视图在某些特定条件下,优化器无法将视图外的谓词条件推送至视图内的基表上,即使在基表的列上创建索引也无法使用索引。本文将对该类视图提出一种优化方案,可以显著提高该类视图的性能,而且表的数据量越大,查询数据越少,性能提高越明显。下面,本文在实操基础上,对影响谓词推入因素及如何优化进行简要阐释。

影响谓词推入因素

本文的实验环境是Oracle 11.2.0.4版本的数据库,笔者通过反复测试发现,若查询语句中存在以下两种情况,则优化器会阻止视图谓词条件推入至基表上:一是视图中的查询中包括了ROWNUM伪列;二是视图中含有分析函数(如ROW_NUMBER() OVER(…))。若遇其它影响谓词推入的情况优化方法与此方法类似。另外,Oracle 11.2.0.4之前的版本可能存在其它影响谓词推入的情况。

优化方法

如果可以将这类视图中常用的谓词条件直接写入视图内,且写成动态的,同时将常用查询列按选择性由大至小创建组合索引,则优化器可以尽可能地使用索引直接获取数据,再进行过滤计算等操作,以此达到优化目的。具体操作步骤如下:

(1)创建一个辅助包,并创建两个函数,实际情况中若同时传入多个条件可自行扩充包中方法,每一个条件需要增加两个方法。

(2)修改视图定义,将谓词条件写入至基表条件中,并重新编译视图。

性能对比

下面通过具体的例子来说明如何对复杂视图进行优化。

通过将原视图和优化后视图进行对比,看出优化后视图的执行计划成本由349降到86,优化效果非常明显。表中红框部分内容需特别引起注意。

优化前视图查询的执行计划是全表扫描,执行效率低下。优化后视图查询的执行计划是索引范围扫描。通过索引直接取出需要访问的数据,再进行后续处理,性能较优化前有大幅提升。

需要指出的是,该方法只在访问视图中少量数据时效果明显,如果访问数据量大,该方法不适用。

表4.1 优化前视图定义、查询语句及执行计划

表4.2 优化后视图定义、查询语句及执行计划

通过以上分析对比可以清晰地看出,优化后的视图可以使用索引且性能有了显著提高。总结起来,对于复杂查询优化思路应该是:如果视图中访问的是少量数据,则尽可能通过索引先取出需要处理的数据,然后再进行后续处理。此方法适用的前提是,复杂视图使用各种常规优化方法(如增加索引,添加提示符等)都无效时。

但是,这一方法也有一定弊端,那就是与其它优化方法相比,此法需要做的前期工作较多,如建立辅助包,对原视图进行修改和重新编译。另外还需测试优化前后查询结果是否一致,查询前需调用辅助包,将查询条件传入视图中,不过优化后的视图是不需要加查询条件的。

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

友情链接