【原创】自己构思2条SQL用于完成相同的功能，并比较它们的性能！

构思：想法比较简单，有3个朋友，编写SQL语句计算一下他们3个月工资的总和
第一种方法就是直接相加得到结果
[oracle@leonarding1 ~]$ sqlplus / as sysdba
SYS@LEO1> conn leo1/leo1
Connected.
LEO1@LEO1> create table leo_wage (id number,name varchar(20),wage number,month date);     创建了 id  姓名 工资 三个字段
Table created.
insert into leo_wage values (1,'leo',100,to_date('2012_01_01','yyyy-mm-dd'));
insert into leo_wage values (2,'leo',200,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_wage values (3,'leo',300,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_wage values (4,'sunev_yu',200,to_date('2012_01_01','yyyy-mm-dd'));
insert into leo_wage values (5,'sunev_yu',300,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_wage values (6,'sunev_yu',400,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_wage values (7,'tigerfish',300,to_date('2012_01_01','yyyy-mm-dd'));
insert into leo_wage values (8,'tigerfish',400,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_wage values (9,'tigerfish',500,to_date('2012_03_01','yyyy-mm-dd'));

LEO1@LEO1> commit;
Commit complete.
LEO1@LEO1> select * from leo_wage;                                    现在记录已经插入了
        ID NAME                     WAGE   MONTH
---------- -------------------- ----------     ---------
         1 leo                         100       01-JAN-12
         2 leo                         200       01-FEB-12
         3 leo                         300       01-MAR-12
         4 sunev_yu                200       01-JAN-12
         5 sunev_yu                300       01-FEB-12
         6 sunev_yu                400       01-MAR-12
         7 tigerfish                  300       01-JAN-12
         8 tigerfish                  400       01-FEB-12
         9 tigerfish                  500       01-MAR-12

LEO1@LEO1> show autotrace;                   查看执行计划，是关闭状态
autotrace OFF
LEO1@LEO1> set autotrace on;                 启动执行计划
LEO1@LEO1> show autotrace;
autotrace ON EXPLAIN STATISTICS              已经启动

LEO1@LEO1> select sum(wage) from leo_wage;
SUM(WAGE)
----------
      2700
Execution Plan
----------------------------------------------------------
Plan hash value: 3461908344
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| LEO_WAGE |     9 |   117 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
直接汇总
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets                                      7次一致性读
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
show autotrace;               显示状态
set autotrace;                语法格式
set autotrace on explain;     即输出sql结果又输出执行计划
set autotrace on statistics;  即输出sql结果又输出统计信息
set autotrace traceonly;      表示不输出sql结果，只输出执行计划和统计信息
小结：看本次有7次一致性读，感觉效率蛮好的。

第二种方法就是先计算出每个人的总和再把每个人总和求和
select sum(sum_wage) from (select name,sum(wage) sum_wage from leo_wage group by name);
LEO1@LEO1> select name,sum(wage) sum_wage from leo_wage group by name;                                                                         先计算出每个人的总和
NAME                   SUM_WAGE
-------------------- ----------
leo                         600
tigerfish                  1200                                            还是tiger工资高，这要请吃饭a
sunev_yu                900
LEO1@LEO1> select sum(sum_wage) from (select name,sum(wage) sum_wage from leo_wage group by name);       再把每个人总和求和，结果和上面的一样
SUM(SUM_WAGE)
-------------
         2700

Execution Plan
----------------------------------------------------------
Plan hash value: 1978181551
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    13 |     4  (25)| 00:00:01 |
|   1 |  SORT AGGREGATE      |          |     1 |    13 |            |          |
|   2 |   VIEW               |          |     9 |   117 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |          |     9 |   225 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| LEO_WAGE |     9 |   225 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
先做分组->排序->汇总
Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         15  consistent gets                                          15次一致性读，一致性读的次数代表sql的性能，一致性读越少性能越好，这个说法前提，两者都没有物理读
          0  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
小结：第二条SQL明显比第一条SQL一致性读次数多了一倍，性能减小了一半，那么在实际中肯定不会这么去写，但这其实可以证明构造2条完全相同功能的SQL由于代码编写的不够优化，它们的性能可能相差甚远。

 

 

Leonarding
2012.11.21
天津 &winter
分享技术~成就梦想
Blog ：http://space.itpub.net/26686207

 

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

转载于:http://blog.itpub.net/26686207/viewspace-749677/