group by 替代distinct
来源：互联网 发布：网络行业分析 编辑：程序博客网 时间：2024/05/29 07:38
SQL> select distinct employee_id,first_name from test1;107 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 255531131------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 6647K| 158M| | 67481 (1)| 00:13:30 || 1 | HASH UNIQUE | | 6647K| 158M| 229M| 67481 (1)| 00:13:30 || 2 | TABLE ACCESS FULL| TEST1 | 6647K| 158M| | 19570 (1)| 00:03:55 |------------------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 4 recursive calls 0 db block gets 142133 consistent gets 71297 physical reads 0 redo size 2749 bytes sent via SQL*Net to client496 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)107 rows processedSQL> select employee_id,first_name from test1 group by employee_id,first_name;107 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1431305903----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 6647K| 158M| 19781 (2)| 00:03:58 || 1 | HASH GROUP BY | | 6647K| 158M| 19781 (2)| 00:03:58 || 2 | TABLE ACCESS FULL| TEST1 | 6647K| 158M| 19570 (1)| 00:03:55 |----------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 4 recursive calls 0 db block gets 142133 consistent gets 71297 physical reads 0 redo size 2749 bytes sent via SQL*Net to client496 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)107 rows processed
