快捷搜索:

解析Oracle数据扫描Oracle SQL优化引导局部扫描方法(6)


相关链接 解析Oracle数据扫描Oracle SQL优化引导局部扫描方法(1) http://www.BkJia.com/database/201205/130424.html; 解析Oracle数据扫描Oracle SQL优化引导局部扫描方法(2) http://www.BkJia.com/database/201205/130428.html; 解析Oracle数据扫描Oracle SQL优化引导局部扫描方法(3) http://www.BkJia.com/database/201205/130432.html; 解析Oracle数据扫描Oracle SQL优化引导局部扫描方法(4) http://www.BkJia.com/database/201205/130439.html; 解析Oracle数据扫描Oracle SQL优化引导局部扫描方法(5) http://www.BkJia.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字段如果存在合适的索引,那么扫描性能会更好,因而整体的查询性能会更高。         通过获得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的专栏

http://www.bkjia.com/oracle/484307.htmlwww.bkjia.comtruehttp://www.bkjia.com/oracle/484307.htmlTechArticle相关链接 解析Oracle数据扫描Oracle SQL优化引导局部扫描方法(1) http://www.2cto.com/database/201205/130424.html; 解析Oracle数据扫描Oracle SQL优化引导...

本文源自: AG环亚集团

您可能还会对下面的文章感兴趣: