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

相关链接

解析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；

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

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

 

通过分离SQL语句实现局部范围数据扫描

 

    通常情况下应该尽量使实现某个功能的SQL语句的各个组成部分放在一起运行比较有效。但是在少数的情况下，使用将SQL分离的方法反倒比较有效。这种情况主要存在于，经常对具有1:M关系的两张表进行查询时，起到对“M”方过滤条件的数据来自于”1”方。通常“M”方的数据会比较多的可能性较大，如果此时为“1”方的表赋予了一个对应数据范围较大的查询条件，则在整个数据处理过程中，就需要付出较大的代价。如下面的语句示例：

 

Select y.deptno,y.ename,y.empno,y.job,x.sal_tot,x.comm_tot

 

From(select empno,sum(sal_amt) sal_tot,sum(comm) comm_tot

 

     From salary s  www.2cto.com  

 

     Where s.deptno like ‘12%’

 

     And s.sal_date between ‘20100101’ and ‘20101231’

 

     Group by empno) x,employee y

 

Where y.empno=x.empno;

 

        该语句实现查询部门编号以“12”开头的部门中每个员工的年工资的总和。这里为了提升查询性能，将deptno字段冗余进了salary表中，同时在salary表上构建了基于deptno和sal_date字段的复合索引（inx_dept_sal），并且我们可以知道employee表和salary表是典型的1:M关系。

 

该语句的驱动查询条件是s.deptno like ‘12%’，此时无法使用局部范围扫描，而且通过该条件扫描数据量很大的salary表，需要读取很多部门的全部员工的工资信息，可想而知代价甚大。不仅如此，尽管查询时使用了salary表上的复合索引inx_dept_sal，但是由于针对复合索引的首字段没有使用等值条件，所以根据组合索引的基本原理，可以知道索引扫描的数据量也会非常大。

 

        通过分析上述语句的执行逻辑，我们可以看出该语句的驱动查询条件其实是来自于employee表的deptno字段，而employee表的数据量相对于salary表来说相对较小。因此如果分离原来的SQL语句，首先按照驱动条件扫描employee表获得需要的deptno信息，然后使用这些deptno作为过滤条件来过滤salary表进而获取最终的查询结果就可能获得较好的查询性能。因为首先可以利用从employee表中获取的deptno使用等值（=）条件来过滤salary表；其次我们可以控制每次按照驱动查询条件检索employee表所返回的数据范围，这样便可以变相的实现了局部范围扫描。因此我们可以如下所示分离原来的SQL语句：

 

Œ     按照驱动查询条件单独扫描employee表：

 

Select deptno into :v_deptno from employee like ‘12%’;

 

此时如果employee表的deptno字段如果存在合适的索引，那么扫描性能会更好，因而整体的查询性能会更高。

 

&＃141;      通过获得deptno信息来过滤salary表的到最终结果：

 

Select y.deptno,y.ename,y.empno,y.job,x.sal_tot,x.comm_tot

  www.2cto.com  

From(select empno,sum(sal_amt) sal_tot,sum(comm) comm_tot

 

    From salary s

 

     Where s.deptno=:v_deptno

 

         And s.sal_date between ‘20100101’ and ‘20101231’

 

         Group by empno) x,employee y

 

Where y.empno=x.empno;

 

      此时对salary表的驱动查询条件变成等值条件，因此可以充分发挥复合索引inx_dept_sal的作用实现性能的提升。

 

这种分离SQL语句的方法，需要注意以下三点：

 

第一、    要准确的识别出应用场合，通常的应用场合是，对具有1:M关系的两张表进行查询时，起到对“M”方过滤条件的数据来自于”1”方，同时“M”方的数据量较大，并且语句需要对“M”进行耗时操作（如：分组统计或排序），同时“1”方的数据量相对较小；

 

第二、    要在重要的过滤字段上构建合适的索引，以便最大幅度提升局部的查询性能；

 

第三、    要对被分离出的首先需要执行的数据扫描进行控制，使之分批返回用于过滤的重要字段信息（如果数据范围较大时），以便实现局部范围扫描；

 

       可见这种方法需要我们在设计上进行更多的思考，同时在实现上也需要更多的附加步骤以及更多的控制性行为（如：分批返回首先进行的小数据量表的扫描结果的手段），但是对比我们所能获得性能提升，这些代价也是值得的。但不管怎么说这种方法都是需要进行代价评估的，因此它的适用范围是有限的，在使用时一定要把住上述的3个注意点来进行合理的设计。

 

       如果不想分离原来的SQL语句，那么此种情形下想提升性能的方法就非常有限了，但是也还是有方法的，此时就需要使用古老的基于规则的优化器了，同时需要在关键过滤字段上存在合理高效的索引。此时可以如下面的方式来实现原来的语句：  www.2cto.com  

 

Select/*+ rule */ y.deptno,y.ename,y.empno,y.job,x.sal_tot,x.comm_tot

 

From(select empno,sum(sal_amt) sal_tot,sum(comm) comm_tot

 

From salary s

 

Where s.deptno in (Select deptno from employee like ‘12%’)

 

  And s.sal_date between ‘20100101’ and ‘20101231’

 

  Group by empno) x,employee y

 

Where y.empno=x.empno;

 

通过使用rule提示，来使得SQL语句按照索引规则执行，首先执行Select deptno from employee like ‘12%’，获取通过s.deptno字段进行索引过滤的所需的信息。还可以如下方式改写：

 

Select y.deptno,y.ename,y.empno,y.job,x.sal_tot,x.comm_tot

 

From(select/*+ index(s inx_dept_sal) */

 

 empno,sum(sal_amt) sal_tot,sum(comm) comm_tot

 

From salary s  www.2cto.com  

 

Where s.deptno in(Select deptno from employee like ‘12%’)

 

  And s.sal_date between ‘20100101’ and ‘20101231’

 

  Group by empno) x,employee y

 

Where y.empno=x.empno;

 

        两种改写的目的都是保证首先执行小数据量的扫描，进而通过利用索引实现对大数据量表的数据过滤。但是后两种的改写是否会在执行过程中生效，这依赖于不同版本的Oracle执行优化器，因此需要进行确认测试，同时后两种改写也不一定会确保进行局部范围数据扫描，但无论如何第一种方式无疑是有效的。

 

 

 

摘自 javacoffe的专栏