解析Oracle数据扫描OracleSQL优化引导局部扫描方法（5）

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（1）

http://www.2cto.com/database/201205/130424.html；

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（2）

http://www.2cto.com/database/201205/130428.html；

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（3）

http://www.2cto.com/database/201205/130432.html；

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法（4）

http://www.2cto.com/database/201205/130439.html

 

利用嵌套视图实现局部范围数据扫描

 

        很显然有些SQL语句只能进行全部范围数据扫描，而无法直接进行局部范围数据扫描，如：排序操作和分组统计操作。这些操作的执行特点决定了必须要得到全部数据范围内的数据，才能够得到最后的执行结果。

 

        但是面对这种SQL语句，我们虽然不能直接引导它们进行局部范围数据扫描，但是我们可以采用间接的手段将这些语句引导成按照局部范围扫描来执行。我们所采取的手段就是利用嵌套视图工具，即可以将使用全部范围扫描的部分捆绑在嵌套视图中，以确保视图之外的部分按照局部范围的方式来使用内部视图返回的数据。该方法的关键就是要从宏观角度出发，寻找出阻止实现局部范围扫描方式的部分，并将这些部分放入嵌套视图中去。这与传统的从表中实现局部范围扫描的主要区别在于，从表中进行局部范围扫描时，所扫描的数据是物理存放在表所在的数据段中的，而通过嵌套视图进行局部范围数据扫描，所扫描的数据来源于动态执行内部嵌套视图查询语句获得的，因此通过嵌套视图来进行局部范围数据扫描转换时，整体语句的执行性能很大程度上取决于内部嵌套视图的查询性能。  www.2cto.com  

 

通过如下示例来看一下如何利用嵌套视图实现局部范围扫描，见如下语句：

 

Select a.deptname,b.empno,b.emp_name,c.sal_ym,c.sal_tot

 

From dept a,employee b,salary c

 

Where a.deptno=b.deptno and c.empno=b.empno

 

And a.location=’BeiJing’ and a.job=’mgr’ and c.sal_ym=’201112’

 

Order by a.dept_name,b.hire_date,c.sal_ym;

 

        该语句实现三张表的关联查询，三张表分别代表部门、员工、员工工资三类信息，该语句实现查询工作与北京的职位为经理的员工在2011年12月的工资信息，并且最终的结果按照部门名称、入职时间和工资月进行排序。该域的实现方式是一种最直接最简单的实现方式，一目了然很直观。但是如果仔细推敲，我们就会发现，内部执行时隐藏着影响查询性的不合理因素。首先该查询最后要求进行排序，那么可知语句无法按照局部范围扫描执行；其次该语句执行3表关联，我们稍加分析一下便可知道，通常一个公司的部门表和员工表的数据量不会太大，但是一个公司的员工工资表通常会包含很大的数据量，因为每个员工的工资会随着工作年限及职位以及某些特殊贡献的不同，通常会有多条不同的工资记录信息，而且该信息量会随着公司管理要求复杂性和公司员工数量扩张而急速扩展。因此如果使用员工表employee与工资表salary进行关联，关联之后的数据行可能会急剧增加，如果这时对这个急剧增加的数据行进行排序并输出结果，那么可想而知最终无法获得较快的执行速度。此时如果我们首先将数据量较小的dept表和employee表进行关联并对其结果进行排序，然后再与salary进行关联，并且此时使用salary表的empno和sal_ym字段进行过滤，同时将这两个字段设计为salary表的复合主键，那么此时不但可以获得同样的查询结果，而且也可以在很大程度上提高查询执行性能。因此我们可以如下所示来改写上述SQL语句：

  www.2cto.com  

Select/*+ ordered,use_nl(x,y) */ x.deptname, x.hire_date,x.empno,x.emp_name,y.sal_ym,y.sal_tot

 

From(select a.deptname,b.empno,b.emp_name

 

From dept a,employee b

 

Where a.deptno=b.deptno And a.location=’BeiJing’ and a.job=’mgr’

 

Order by a.dept_name,b.hire_date) x,salary y

 

Where y.empno=x.empno and y.sal_ym=’201112’;

 

该语句中通过Hint保证嵌套视图中的查询被优先执行，然后其结果与salary表进行关联，先对数据量较小的表进行全部范围数据扫描执行两表关联，然后按照局部范围扫描方式在对数据量较大的表进行处理。在该操作中除了引导进行局部范围扫描外，关键过滤字段的索引选择性对最终的查询性能也有很大的作用。如dept表的location和job字段，以及salary表的(empno, sal_ym)复合主键，如果这些字段上拥有合适的索引，那么会对最终的查询性能起到很大的帮助。

 

       下面我们看一下如何引导分组操作进行局部范围扫描，引导分组操作的进行局部范围扫描的一种方式就是使用标量子查询。看如下语句：

 

Select a.product_cd,a.product_name,b.avg_stock

 

From product a,

 

    (select product_cd,sum(stock_qty)/10 avg_stock

 

     From prod_stock  www.2cto.com  

 

     Where stock_date between to_date(‘2010-06-01’,’yyyy-mm-dd’) and

 

                            to_date(‘2011-04-01’,’yyyy-mm-dd’)

 

     group by product_cd) b

 

where b.product_cd=a.product_cd

 

and a.category_cd=’20’;

 

由于该语句按照全部范围扫描的方式来对大范围的prod_stock数据进行分组操作，因此执行性能较差，但我们可以通过使用标量子查询引导该语句进行局部范围扫描，来提升查询性能。见如下改写后的语句：

 

Select a.product_cd,a.product_name,(select sum(stock_qty)/10

 

                               From prod_stock b

 

                               Where b.product_cd=a.product_cd

 

 stock_date between to_date(‘2010-06-01’,’yyyy-mm-dd’) and

  www.2cto.com  

                                to_date(‘2011-04-01’,’yyyy-mm-dd’)

 

) avg_stock

 

From product a

 

Where a.category_cd=’20’;

 

此处应该注意的是，标量子查询并不是所有的版本都会支持，所以使用之前要进行测试确认。

 

 

 

摘自 javacoffe的专栏