OR EXISTS语句的优化方法

OR EXISTS语句的优化方法：

这库一直很空闲，但无意中看了一下，发现其中很多语句都很有问题，都是典型的OR问题语句，如果并发量大的话，CPU一下子就飙高了。

OR语句一直是性能杀手，当存在一两个的时候一般可以用union和union all来优化，请看以下例子。

1.在原句中使用了or语句并且or语句里面使用exists语句，这样给优化器造成了很大的迷惑。

2.从执行计划看来，貌似没有什么问题。

3.但从统计数据来看，一致读非常高，达到了4百多万次。

select count(*)

  from UNIOMS0808.SETTLEMENT s

 where (settlementStatus = 3)

   and (companyCode like '%GDSPID01687%' or exists

        (select 1

           from UNIOMS0808.PROVIDER pro

          where s.companyCode = pro.cpId

            and pro.companyId in

                (select com.companyId

                   from UNIOMS0808.COMPANY com

                  where com.companyCode like '%GDSPID01687%')));

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=164 Card=1 Bytes=14)

   1    0   SORT (AGGREGATE)

   2    1     FILTER

   3    2       TABLE ACCESS (FULL) OF 'SETTLEMENT' (Cost=164 Card=1939 Bytes=27146)

   4    2       NESTED LOOPS (Cost=375 Card=190 Bytes=6840)

   5    4         TABLE ACCESS (FULL) OF 'PROVIDER' (Cost=20 Card=355 Bytes=4615)

   6    4         TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY' (Cost=1 Card=1 Bytes=23)

   7    6           INDEX (UNIQUE SCAN) OF 'PK_COMPANY' (UNIQUE)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

    4726442  consistent gets

          0  physical reads

          0  redo size

        406  bytes sent via SQL*Net to client

        503  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

优化思路：直接将or语句转换为union语句观察一下。

select count(1)

from 

(

select SETTLEMENTID

  from UNIOMS0808.SETTLEMENT s

 where (settlementStatus = 3)

   and (companyCode like '%GDSPID01687%')

union

select SETTLEMENTID

  from UNIOMS0808.SETTLEMENT s

 where (settlementStatus = 3)

   and exists(select 1

           from UNIOMS0808.PROVIDER pro, UNIOMS0808.COMPANY com

          where s.companyCode = pro.cpId

            and pro.companyId =com.companyId

            and com.companyCode like '%GDSPID01687%')

);

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=376 Card=1)

   1    0   SORT (AGGREGATE)

   2    1     VIEW (Cost=376 Card=1199)

   3    2       SORT (UNIQUE) (Cost=376 Card=1199 Bytes=37355)

   4    3         UNION-ALL

   5    4           TABLE ACCESS (FULL) OF 'SETTLEMENT' (Cost=164 Card=994 Bytes=24850)

   6    4           HASH JOIN (Cost=199 Card=205 Bytes=12505)

   7    6             NESTED LOOPS (Cost=34 Card=14 Bytes=504)

   8    7               TABLE ACCESS (FULL) OF 'PROVIDER' (Cost=20 Card=14 Bytes=182)

   9    7               TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY' (Cost=1 Card=1 Bytes=23)

  10    9                 INDEX (UNIQUE SCAN) OF 'PK_COMPANY' (UNIQUE)

  11    6             TABLE ACCESS (FULL) OF 'SETTLEMENT' (Cost=164 Card=19884 Bytes=497100)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1917  consistent gets

          0  physical reads

          0  redo size

        406  bytes sent via SQL*Net to client

        503  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

观察效果：效果非常明显，一致读下降到2千个，是原来的千分之0.4。从执行计划中看最大不同在于新的执行计划使用了hash join，而且显示的cost比原来的还要大。

问题分析：由于or exists语句导致优化器选择了nested loops进行表连接，而nested loops对于少数据量处理是很好的，但对于全表扫描来说则效率更低，最终导致大量的一致读产生。

有兴趣的可以进一步使用10046事件进行分析，确定问题的症结。

以下提供可重现的测试方法

create table test as select * from dba_objects;

create table test1 as select * from dba_objects;

优化前

select count(*)

  from test s

 where (OBJECT_TYPE = 'VIEW')

   and (OBJECT_NAME like '%DBA_TABLES%' or exists

        (select 1

           from test1 pro

          where s.object_id = pro.object_id

            and pro.OBJECT_NAME = 'DBA_TABLES'));

            

优化后

select count(1)

from 

(

select object_id

  from test s

 where (OBJECT_TYPE = 'VIEW')

   and (OBJECT_NAME like '%DBA_TABLES%')

UNION   

select object_id

  from test s

 where (OBJECT_TYPE = 'VIEW')

   and exists

        (select 1

           from test1 pro

          where s.object_id = pro.object_id

            and pro.OBJECT_NAME = 'DBA_TABLES')

);            

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13605188/viewspace-706829/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13605188/viewspace-706829/