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

转载 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秒,取得了较好的效果.

Spark SQL模块代码分析(查询语句到逻辑查询计划树的过程)

SQLContext.executeSql          SQLContext.parseSql                    DDLParser//先用DDL解析器解析 ...
  • huxuanlai
  • huxuanlai
  • 2017年03月02日 14:35
  • 478

深入了解MySQL-执行计划优化

存储引擎 Attribute MyISAM Heap BDB InnoDB Transactions No No Ye...
  • duxingxia356
  • duxingxia356
  • 2014年11月27日 12:24
  • 1047

【MySql性能优化二】利用explain进行查询和分析sql语句

在mysql数据库中为我们提供了explain方法可以通过它来帮助我们分析我们的sql语句。 登录mysql后,具体使用如下这里的了例子都以上篇博客中安装的实例数据库sakila为例: explain...
  • wangyy130
  • wangyy130
  • 2016年05月16日 11:38
  • 2161

通过分析SQL语句的执行计划优化SQL (四)

第4章 ORACLE的优化器        优化器有时也被称为查询优化器,这是因为查询是影响数据库性能最主要的部分,不要以为只有SELECT语句是查询。实际上,带有任何WHERE条件的DML(INSE...
  • mustbelove
  • mustbelove
  • 2006年11月20日 17:29
  • 677

优化必不可少--看懂SqlServer查询计划

对于SqlServer的优化来说,可能优化查询是很常见的事情。关于数据库的优化,本身也是一个涉及面比较的广的话题,本文只谈优化查询时如何看懂SqlServer查询计划。由于我对SqlServer的认识...
  • t134679
  • t134679
  • 2011年10月03日 12:06
  • 4813

查询处理器未能用优化程序生成查询计划,因为查询无法同时更新聚集键和 text、ntex

错误:查询处理器未能用优化程序生成查询计划,因为查询无法同时更新聚集键和 text、ntex原因分析:由于ec_cms_article表中涉及几个text格式,在索引时,同时设置articleid,c...
  • liuyunfan
  • liuyunfan
  • 2010年03月14日 09:35
  • 1359

Mysql查询性能优化-善用Explain语句

Mysql查询性能优化-善用Explain语句     在项目中验证sql语句执行效率的时候最直观的方式就是查看其执行时间,但是在线上环境中如果不慎运行一个效率十分低下的sql导致数据库down掉了,...
  • u012091092
  • u012091092
  • 2016年11月28日 14:50
  • 2607

Mysql常用30种SQL查询语句优化方法

1、应尽量避免在 where 子句中使用!=或 2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3、应尽量避免在 where 子句中对...
  • youthsunshine
  • youthsunshine
  • 2016年12月05日 15:32
  • 2423

查询分析器里查看执行计划

当需要分析某个查询的效能时,最好的方式之一查看这个查询的执行计划。执行计划描述SQL Server查询优化器如何实际运行(或者将会如何运行)一个特定的查询。   查看查询的执行计划有几种不同的方式...
  • anchenyanyue
  • anchenyanyue
  • 2011年09月22日 15:26
  • 1978

mysql中如何查看优化器优化后的执行计划

test>desc items; +-------------+--------------+------+-----+---------+----------------+ | Field   ...
  • aoerqileng
  • aoerqileng
  • 2016年04月23日 11:00
  • 300
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:根据查询计划优化查询语句
举报原因:
原因补充:

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