sql语句在CBO优化器下语句中出现聚合函数和分组函数，如何编写SQL更好的使用索引

Liuxuebo

         在工作中尤其是在维护一个已经上线运行的数据库系统的DBA来说，除了平时的排错工作中最重要的部分就是优化了。有的是开发人员编写的SQL有问题，这时要求DBA对SQL的执行原理和顺序要有很好的理解。
下面讨论一下在SQL语句中出现聚合函数和group by  和 where时SQL编写时怎么样更有效
    业务表  df_url_gzrz 是一个分区表，数据量很大，在shijian  列上有一个普通的 B 树索引 。客户要每小时的统计，下面是抓取到SQL 修改时间后得到的SQL，测试SQL 得到的执行计划和时间
SQL> alter session set sql_trace=true;
Session altered.
Elapsed: 00:00:00.00
SQL> set autotrace on;
SQL> select YL,count(*)
  2  from df_url_gzrz
  3  where
  4     shijian>to_date('2014-09-29 15:17:00','yyyy-mm-dd HH24:mi:ss')
  5  and
  6     shijian< to_date('2014-09-29 16:17:00','yyyy-mm-dd hh24:mi:ss')
  7     group by YL;
YL                               COUNT(*)
------------------------------ ----------
广州电信同和CN2                      2350
北京教育清华CERNET                   6179
广州移动花果山CM                    74536

                               .
                               .
                               .
                               .
24 rows selected.
Elapsed: 00:02:09.90
Execution Plan
----------------------------------------------------------
Plan hash value: 1275050054
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |    22 |   550 | 25394   (1)| 00:05:05 |       |       |        |      |            |
|   1 |  PX COORDINATOR          |             |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001    |    22 |   550 | 25394   (1)| 00:05:05 |       |       |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY         |             |    22 |   550 | 25394   (1)| 00:05:05 |       |       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |             |    22 |   550 | 25394   (1)| 00:05:05 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000    |    22 |   550 | 25394   (1)| 00:05:05 |       |       |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY      |             |    22 |   550 | 25394   (1)| 00:05:05 |       |       |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |             |  1667K|    39M| 25376   (1)| 00:05:05 |   124 |   124 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| DF_URL_GZRZ |  1667K|    39M| 25376   (1)| 00:05:05 |   124 |   124 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   8 - filter("SHIJIAN">TO_DATE('2014-09-29 15:17:00', 'yyyy-mm-dd hh24:mi:ss') AND "SHIJIAN"<TO_DATE('2014-09-29 16:17:00',
              'yyyy-mm-dd hh24:mi:ss'))
有执行计划可以很明显的看到  SQL  并没有走索引，而是一开始就进行了全表扫描。而且还做了两次HASH GROUP BY        ，此时oracle 先进行了全表扫描  group by 分组，然后最后得到  符合条件的结果。很显然这种执行方式并不是我们所要的。

下面看看 修改后的语句
select YL,count(*) from (
   select YL,shijian from df_url_gzrz
   where
   shijian>to_date('2014-09-29 15:17:00','yyyy-mm-dd HH24:mi:ss')
   and
    shijian< to_date('2014-09-29 16:17:00','yyyy-mm-dd hh24:mi:ss')
   ) group by YL;
YL                               COUNT(*)
------------------------------ ----------
广州电信同和163                    252020
北京铁通西客站CRNET                 10241
上海电信信息园163                  248297



24 rows selected.
Elapsed: 00:00:01.42
Execution Plan
----------------------------------------------------------
Plan hash value: 453622093
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |    23 |   575 |    54 (100)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                      |                     |    23 |   575 |    54 (100)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE SINGLE            |                     |  1664K|    39M|     0   (0)| 00:00:01 |    62 |    62 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| DF_URL_GZRZ         |  1664K|    39M|     0   (0)| 00:00:01 |    62 |    62 |
|*  4 |     INDEX RANGE SCAN                | DF_URL_GZRZ_SHIJIAN | 30504 |       |     0   (0)| 00:00:01 |    62 |    62 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("SHIJIAN">TO_DATE('2014-09-29 15:17:00', 'yyyy-mm-dd hh24:mi:ss') AND "SHIJIAN"<TO_DATE('2014-09-29
              16:17:00', 'yyyy-mm-dd hh24:mi:ss'))

从执行计划中我们可以看到  SQL 语句是开始就执行的子查询走索引，得到一个小的结果集，在得到小的结果集后，oracle 有进行的全表扫描，依据ROWID  最后执行时间为2秒左右


  所以我们在编写SQL时，在SQL 语句中含有聚合函数和分组函数时，oracle  CBO  基于成本的优化器不一定 先执行什么 再执行什么，所以 在这种情况下 首先尽可能最快把结果集缩小  ，然后再让  分组 再聚合
         

高景KK

第一句SQL 把count（*）换成常量看看走不走索引，看看是group by 的原因还是带了count的原因

llto521

学习CCNA,CCNP,CCIE技术就来www.santongit.com！

zhu_thunder

太棒了，写的很好，我很喜欢。

上弦

学习中，谢谢

未来罐

爱技术,爱实验,尽在三通IT学院!

moonligh

大神求带走

moonligh

大神求带走

moonligh

大神求带走

moonligh

大神求带走