根据查询计划优化查询语句

转载 2013年12月02日 14:46:08

一般在oracle的查询语句中,如果碰到两个表进行关联,在使用基于规则的优化程序执行检索时,基本的一个思路是使用中间结果较少的表做driving table.但也有特例.

    环境描述:

    表PCR中有2w条数据记录,其中符合条件pcr.channel_id=’31’的记录条数不足2000,字段party_id, channel_id上建有联合索引.

    表OI上的记录行数超过1000w条,其中符合条件ORDER_DATE_ID BETWEEN '20080801' AND '20080803'的记录条数约5W条,而oi表的distributor_id上不具有单据索引,且该列选择性较差,不适合建立索引.

    考虑下面的查询.

    SELECT OI.ORDER_DATE_ID,

           OI.DISTRIBUTOR_ID,

           OI.PRODUCT_ID,

           SUM(OI.QUANTITY),

           SUM(OI.AMOUNT)

      FROM ODS.CR_PARTY_CHANNEL_RELATIONSHIP PCR,

           ODS.CR_ORDER_INFO                 OI

     WHERE OI.DISTRIBUTOR_ID = PCR.PARTY_ID

       AND PCR.CHANNEL_ID = '31'

       AND OI.ORDER_TYPE_ID IN ('SALES_ORDER''SALES_RETURN_ORDER')

       AND OI.ORDER_DATE_ID BETWEEN '20080801' AND '20080803'

       GROUP BY OI.ORDER_DATE_ID, OI.DISTRIBUTOR_ID, OI.PRODUCT_ID;

    Oracle给出的查询执行计划如下:

SELECT STATEMENT, GOAL = CHOOSE                                                              326                 2                      188

 SORT GROUP BY                                                          326                 2                      188

  TABLE ACCESS BY INDEX ROWID                   ODS               CR_ORDER_INFO          318                 2                      100

   NESTED LOOPS                                                        320                 2                      188

    TABLE ACCESS FULL                  ODS               CR_PARTY_CHANNEL_RELATIONSHIP            2                      1                      44

    INDEX RANGE SCAN                   ODS               IDX_CR_ORDER_INFO_X1                38                   8371             

可以看出,oracle自动选择结果较小的表pcr作为driving table.但这种情况下由于oi的distribution_id上不具有索引(即使具有索引,也不能使用,因为day_id的索引选择性更高且更有效).索引导致两个关联表做nest loops时会遍历表oi产生的中间结果集,降低了查询执行的效率.

     结合表的特点和索引结构,我们认为,优化查询效率的关键步骤是使用oi表作为驱动表,这样在两个表进行关联的时候可以使用pcr上的索引.并且由于我们的最终结果中不包含pcr表的数据,所以可以只扫描pcr的索引数据,而避免对表pcr的实际记录内容进行查询.

     最终我们给出的查询语句如下.

    SELECT OI.ORDER_DATE_ID,

             OI.DISTRIBUTOR_ID,

             OI.PRODUCT_ID,

             SUM(OI.QUANTITY),

             SUM(OI.AMOUNT)

        FROM ODS.CR_ORDER_INFO OI

       WHERE EXISTS

       (SELECT /*+index(PCR IDX_CR_P_C_RELATIONSHIP)*/

               1

                FROM ODS.CR_PARTY_CHANNEL_RELATIONSHIP PCR

               WHERE OI.DISTRIBUTOR_ID = PCR.PARTY_ID

                 AND PCR.CHANNEL_ID = '31')

         AND OI.ORDER_TYPE_ID IN ('SALES_ORDER''SALES_RETURN_ORDER')

         AND OI.ORDER_DATE_ID BETWEEN '20080801' AND '20080803'

       GROUP BY OI.ORDER_DATE_ID, OI.DISTRIBUTOR_ID, OI.PRODUCT_ID

执行计划为:

SELECT STATEMENT, GOAL = CHOOSE                           353       2         188

 SORT GROUP BY                         353       2         188

  HASH JOIN SEMI                       347       2         188

   TABLE ACCESS BY INDEX ROWID         ODS       CR_ORDER_INFO      319       3348      167400

    INDEX RANGE SCAN          ODS       IDX_CR_ORDER_INFO_X1         39        8371     

   INDEX FULL SCAN  ODS       IDX_CR_P_C_RELATIONSHIP      26        1         44

本例中,优化前后数据的执行时间从60秒减少到3秒,取得了较好的效果.

SQL查询语句的性能优化

  • 2013年11月29日 12:59
  • 165KB
  • 下载

Mysql 查询语句优化原则

mysql是web开发人员使用最多的数据库之一,在网站系统整体优化的过程中,针对数据库的优化又是最重要的一个环节,本文结合mysql性能优化一书整理了当中一些常用方法和原则。一般来说,Query 语句...

30 种 SQL 查询语句的优化方法

在参与实际项目中,当 MySQL 表的数据量达到百万级时,普通的 SQL 查询效率呈直线下降,而且如果 where 中的查询条件较多时,其查询速度无法容忍。因此如何提高 SQL 语句查询效率,显得十分...
  • ltaihyy
  • ltaihyy
  • 2017年10月24日 17:18
  • 127

MySql之优化查询语句

一,减少重复查询    SELECT t.province AS '省份',t.city as '城市',(select count(DISTINCT accountId) from try_appl...

mysql优化limit查询语句的5个方法

mysql的分页比较简单,只需要limit offset,length就可以获取数据了,但是当offset和length比较大的时候,mysql明显性能下降1.子查询优化法先找出第一条数据,然后大于等...

oracle查询语句优化

本文转自 http://www.cnblogs.com/cnjava/archive/2013/02/28/2937699.html Oracle查询性能优化 原则一:注意...

查询语句优化,从48秒到0.3秒

网站系统上线至今,数据量已经不知不觉上到500M,近8W记录了。涉及数据库操作的基本都是变得很慢了,用的人都会觉得躁火~~然后把这个情况在群里一贴,包括机器配置什么的一说,马上就有群友发话了,而且帮我...

sql查询语句优化

1:选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最...

mysql优化limit查询语句的5个方法

这篇文章主要介绍了mysql优化limit查询语句的5个方法,它们分别是子查询优化法、倒排表优化法、反向查找优化法、limit限制优化法和只查索引法,需要的朋友可以参考下 mysql的分页比较简单,...

优化遍历查询语句(编程珠玑)

http://blog.jobbole.com/109729/?utm_source=tuicool&utm_medium=referral 在一个数组中查找某一个元素,或是在一个字符串中查...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:根据查询计划优化查询语句
举报原因:
原因补充:

(最多只允许输入30个字)