通过分离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
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 where deptno like ‘12%’;
此时如果employee表的deptno字段如果存在合适的索引,那么扫描性能会更好,因而整体的查询性能会更高。
通过获得deptno信息来过滤salary表的到最终结果:
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
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语句,那么此种情形下想提升性能的方法就非常有限了,但是也还是有方法的,此时就需要使用古老的基于规则的优化器了,同时需要在关键过滤字段上存在合理高效的索引。此时可以如下面的方式来实现原来的语句:
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
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执行优化器,因此需要进行确认测试,同时后两种改写也不一定会确保进行局部范围数据扫描,但无论如何第一种方式无疑是有效的。