DISTINCT vs GROUP BY-TOMSYAN-ChinaUnix博客

在测试过程中发现 DISTINCT 和 GROUP BY 性能差不多，本打算把下面的话贴给Tom，打算问2个的主要区别,发现上面已经有人问过了，而且TOM也给了例子

Hi tom:

See the following example:

SQL> create table test as select * from all_objects;

Table created.

SQL> set linesize 300
SQL> set autot traceonly
SQL> select distinct object_type from test;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3279290493

-----------------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   472K| 5076K|       | 3535   (2)| 00:00:43 |
|   1 | HASH UNIQUE       |      |   472K| 5076K|    18M| 3535   (2)| 00:00:43 |
|   2 |   TABLE ACCESS FULL| TEST |   472K| 5076K|       | 1448   (1)| 00:00:18 |
-----------------------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
          4 recursive calls
          0 db block gets
       6535 consistent gets
          0 physical reads
          0 redo size
       1079 bytes sent via SQL*Net to client
        503 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         23 rows processed

SQL> select object_type from test group by object_type;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2408827628

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   472K| 5076K| 1479   (3)| 00:00:18 |
|   1 | HASH GROUP BY     |      |   472K| 5076K| 1479   (3)| 00:00:18 |
|   2 |   TABLE ACCESS FULL| TEST |   472K| 5076K| 1448   (1)| 00:00:18 |
---------------------------------------------------------------------------

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

The above two SQL does the same thing，but with different execution plan, One is "HASH UNIQUE",and the other is "HASH GROUP BY" 。
From the above ,the second one seems better due to less logical read。

but i run the above two SQL with many time and find the two SQL have the same logical read .

SQL> select distinct object_type from test;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3279290493

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

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       6451 consistent gets
          0 physical reads
          0 redo size
       1079 bytes sent via SQL*Net to client
        503 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         23 rows processed

SQL> select object_type from test group by object_type;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2408827628

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

I want to know ,Which one is better for performance?

Thanks in advance.

一下是TOM大师给的例子:

You Asked

Tom,

Just want to know the difference between DISTINCT and GROUP BY in queries where I'm not

using any aggregate functions.

Like for example.

Select emp_no, name from Emp

Group by emo_no, name

And

Select distinct emp_no, name from emp;

Which one is faster and why ?

Thanks

and we said...

they are for all intents and purposes the same... and really easy for you to

evaluate!!

if you run:

set linesize 121

set echo on

drop table t;

create table t

select * from all_objects;

alter table t add constraint t_pk primary key(object_id);

exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );

set autotrace traceonly

select distinct owner, object_name, object_type from t;

select owner, object_name, object_type from t group by owner, object_name, object_type;

set autotrace off

alter session set sql_trace=true;

set autotrace traceonly

select distinct owner, object_name, object_type from t;

select owner, object_name, object_type from t group by owner, object_name, object_type;

set autotrace off

select distinct owner, object_name, object_type from t

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 3 0.00 0.00 0 0 0 0

Execute 3 0.00 0.00 0 0 0 0

Fetch 9525 0.92 0.83 0 1992 0 142815

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 9531 0.92 0.83 0 1992 0 142815

Rows Row Source Operation

------- ---------------------------------------------------

47605 SORT UNIQUE (cr=664 pr=0 pw=0 time=177034 us)

47938 TABLE ACCESS FULL T (cr=664 pr=0 pw=0 time=48087 us)

********************************************************************************

select owner, object_name, object_type from t group by owner, object_name, object_type

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 3 0.00 0.00 0 0 0 0

Execute 3 0.00 0.00 0 0 0 0

Fetch 9525 0.93 0.85 0 1992 0 142815

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 9531 0.93 0.85 0 1992 0 142815

Rows Row Source Operation

------- ---------------------------------------------------

47605 SORT GROUP BY (cr=664 pr=0 pw=0 time=166792 us)

47938 TABLE ACCESS FULL T (cr=664 pr=0 pw=0 time=48006 us)

以下是问题的地址：