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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24496749/viewspace-730076/