ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle sql调优系列6(group by)

oracle sql调优系列6(group by)

原创 Linux操作系统 作者:alsrt 时间:2012-06-08 11:22:28 0 删除 编辑
在网上看到一个关于group by的案例,使用group by性能有相当大的提升,我尝试着构造一个案例来模拟这种现象
 
 
首先我们些准备工作
 
 exec dbms_stats.delete_table_stats('SCOTT','EMP')
 
disable emp的主键索引,目的是去掉index的干扰
 
alter table emp disable constraint pk_emp;
 
开始测试:
 
编造一个显示每个部门20.30.10的全部员工的sql语句
 
语句1如下:
 
select '10',count(empno) from emp e where deptno=10
union
select '20',count(empno) from emp e where deptno=20
union
select '30', count(empno) from emp e where deptno=30
 
执行计划:
 
 
SQL> /
'1 COUNT(EMPNO)
-- ------------
10            3
20            5
30            6

Execution Plan
----------------------------------------------------------
Plan hash value: 2532348403
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |    78 |    12  (75)| 00:00:01 |
|   1 |  SORT UNIQUE         |      |     3 |    78 |    12  (75)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|   3 |    SORT AGGREGATE    |      |     1 |    26 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| EMP  |     3 |    78 |     3   (0)| 00:00:01 |
|   5 |    SORT AGGREGATE    |      |     1 |    26 |     4  (25)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL| EMP  |     5 |   130 |     3   (0)| 00:00:01 |
|   7 |    SORT AGGREGATE    |      |     1 |    26 |     4  (25)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL| EMP  |     6 |   156 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("DEPTNO"=10)
   6 - filter("DEPTNO"=20)
   8 - filter("DEPTNO"=30)
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
--------------------------------------------------------------------------------------------------------
 
使用group by 语句2
 
select deptno,count(empno) from emp group by deptno;
 
 
执行计划:
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   364 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |    14 |   364 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
 
 
 
 
看来还是用group by 做分组好点
 
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8410760/viewspace-732256/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-02

  • 博文量
    34
  • 访问量
    35303