oracle sql调优系列6(group by)

在网上看到一个关于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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8410760/viewspace-732256/