【第22期】观点:IT 行业加班,到底有没有价值?

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

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

举报

相关文章推荐

Oracle的分页查询语句優化

http://kingsen5.blog.163.com/blog/static/189301290201142591225277/ Oracle的分页查询语句基本上可以按照本文给出的格式来进行...

Oracle的分页查询语句优化

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。 (一) 分页查询格式: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM = 21 其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM = 21控制分页查询的每页的范围。 上面给出的这个分页查询语句,在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小,
  • sunx
  • sunx
  • 2013-08-07 16:56
  • 367

欢迎关注CSDN程序人生公众号

关注程序员生活,汇聚开发轶事。

如何优化SQL查询语句?

SQL数据库是我们开发中最常用的数据库之一,那在开发中我们如何才能优化SQL查询,写出高性能的SQL语句呢?本文就和大家一起来扒一扒这部分内容,希望对大家学习SQL数据库有所帮助。   1、 首...

查询语句(SELECT)的优化

您正在看的ORACLE教程是:查询语句(SELECT)的优化。   这篇文章是基于Informix数据库写的。希望对大家有用。          程序设计中的一个著名定律是20%的代码用去了80%的时间,在数据库应用程序中也同样如此。数据库应用程序的优化通常可分为两个方面:源代码的优化和 SQL语句的优化。源代码的优化在时间成本和风险上代价很高;另一方面,源代码的优化对数据库系统性能的提升收效有限。   许多程序员认为查询优 化是DBMS(数据库管理系统)的任务,与程序员所编写的SQL语句关系不大,这是错误的。一个好的查询计划往往可以使程
  • sbpya
  • sbpya
  • 2007-10-28 02:42
  • 699

CPU瓶颈(二)--过度的查询语句编译及优化

查询语句编译及优化是一项CPU密集处理操作。查询语句优化的花费会因为语句的复杂性及基础架构的增长而增加,但是即使是一个非常简单的查询语句也可以花费CPU10-20毫秒的时间去解析和编译。 为了减缓这...
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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