三通IT学院 门户 数据库 查看内容

2014-10-17 14:19
sql语句在CBO优化器下语句中出现聚合函数和分组函数,如何编写SQL更好的使用索引

摘要 : 在工作中尤其是在维护一个已经上线运行的数据库系统的DBA来说,除了平时的排错工作中最重要的部分就是优化了。有的是开发人员编写的SQL有问题,这时要求DBA对SQL的执行原理和顺序要有很好的理解。 下面讨论一下在SQ ...

时光与梦2014-10-17 14:1951218
原作者: 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 2014-10-16 19:23
第一句SQL 把count(*)换成常量看看走不走索引,看看是group by 的原因还是带了count的原因
引用 llto521 2014-10-16 19:52
学习CCNA,CCNP,CCIE技术就来www.santongit.com
引用 zhu_thunder 2014-10-17 10:14
太棒了,写的很好,我很喜欢。
引用 上弦 2014-10-30 19:16
学习中,谢谢
引用 未来罐 2015-2-26 08:54
爱技术,爱实验,尽在三通IT学院!
引用 moonligh 2015-4-25 09:24
大神求带走
引用 moonligh 2015-4-25 09:25
大神求带走
引用 moonligh 2015-4-25 09:25
大神求带走
引用 moonligh 2015-4-25 09:26
大神求带走
引用 ReturnDream 2015-5-1 20:12
henfuzadeyangzi
引用 jaslee 2015-6-17 16:37
极客学院安卓Android视频教程第二季 Java基础篇
引用 Zeus 2016-9-29 11:20
fffffffffffffffffffff
引用 未来罐 2016-9-30 07:09
爱技术,爱实验,尽在三通IT学院!
引用 未来罐 2016-10-2 15:49
太棒了,写的很好,我很喜欢。
引用 stit2012 2016-10-5 10:42
谢谢分享。。。

查看全部评论(18)

返回顶部