ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一条语句使用group by前后的变化

一条语句使用group by前后的变化

原创 Linux操作系统 作者:shuangoracle 时间:2012-05-17 22:59:19 0 删除 编辑
drop table test1;
drop table test2;

create table test1 
as
select object_id, object_name from dba_objects;
多执行几次
insert into test1 select * from test1;
select count(1) from test1;--796368

create table test2
as
select object_id, object_name from dba_objects where rownum < 100;
多执行几次 
insert into test2 select * from test2;
select count(1) from test2;--405504

然后看下面几个sql语句的执行计划和逻辑/物理读信息


SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from test1 where exists (select object_id from test2 where test1.object_id=test2.object_id);

1584 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 981117694

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    99 |  9108 | 14759   (1)| 00:02:58 |
|*  1 |  HASH JOIN          |       |    99 |  9108 | 14759   (1)| 00:02:58 |
|   2 |   SORT UNIQUE       |       |   371K|  4712K|   228   (4)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| TEST2 |   371K|  4712K|   228   (4)| 00:00:03 |
|   4 |   TABLE ACCESS FULL | TEST1 |   441K|    33M| 12729   (1)| 00:02:33 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
       1181  recursive calls
          0  db block gets
      59290  consistent gets
      58886  physical reads
          0  redo size
      34450  bytes sent via SQL*Net to client
       1540  bytes received via SQL*Net from client
        107  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
       1584  rows processed
SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from test1 where object_id in (select object_id from test2);

1584 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 981117694

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    99 |  9108 | 14759   (1)| 00:02:58 |
|*  1 |  HASH JOIN          |       |    99 |  9108 | 14759   (1)| 00:02:58 |
|   2 |   SORT UNIQUE       |       |   371K|  4712K|   228   (4)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| TEST2 |   371K|  4712K|   228   (4)| 00:00:03 |
|   4 |   TABLE ACCESS FULL | TEST1 |   441K|    33M| 12729   (1)| 00:02:33 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"="OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        783  recursive calls
          0  db block gets
      59224  consistent gets
      58879  physical reads
          0  redo size
      34450  bytes sent via SQL*Net to client
       1540  bytes received via SQL*Net from client
        107  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
       1584  rows processed

SQL> select * from test1 where object_id in (select object_id from test2 group by object_id);


1584 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1692032541

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    92 | 14797   (2)| 00:02:58 |
|*  1 |  HASH JOIN           |          |     1 |    92 | 14797   (2)| 00:02:58 |
|   2 |   VIEW               | VW_NSO_1 |   371K|  4712K|   266  (17)| 00:00:04 |
|   3 |    HASH GROUP BY     |          |     1 |  4712K|   266  (17)| 00:00:04 |
|   4 |     TABLE ACCESS FULL| TEST2    |   371K|  4712K|   228   (4)| 00:00:03 |
|   5 |   TABLE ACCESS FULL  | TEST1    |   441K|    33M| 12729   (1)| 00:02:33 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"="$nso_col_1")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
       1207  recursive calls
          0  db block gets
      59850  consistent gets
      58886  physical reads
          0  redo size
      34450  bytes sent via SQL*Net to client
       1540  bytes received via SQL*Net from client
        107  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
       1584  rows processed

一条sql语句使用group by确实比不使用消耗更多一些,但是执行时间却比不使用甚至更短。先记录一下,有空仔细做研究。

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

上一篇: dbms_utility.get_time
请登录后发表评论 登录
全部评论

注册时间:2010-08-25

  • 博文量
    84
  • 访问量
    208606