Hi,
Does DISTINCT keyword cause extra overhead in query execution, since the Optimizer has to fetch distinct values from already fetched output?
Example:
SQL> select object_type from user_objects;
Execution Plan
----------------------------------------------------------
Plan hash value: 335614952
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1608 | 17688 | 1195(1)| 00:00:13 |
| 1 | VIEW | USER_OBJECTS | 1608 | 17688 | 1195(1)| 00:00:13 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1858 | 132K| 1195(1)| 00:00:13 |
|* 5 | INDEX RANGE SCAN | I_OBJ2 | 1858 | | 61(2)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2(0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1(0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select distinct object_type from user_objects;
Execution Plan
----------------------------------------------------------
Plan hash value: 2681509383
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1608 | 17688 | 1197 (1)| 00:00:13 |
| 1 | HASH UNIQUE | | 1608 | 17688 | 1197 (1)| 00:00:13 |
| 2 | VIEW | USER_OBJECTS | 1608 | 17688 | 1195 (1)| 00:00:13 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1858 | 132K| 1195 (1)| 00:00:13 |
|* 6 | INDEX RANGE SCAN | I_OBJ2 | 1858 | | 61 (2)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
An extra overhead is created for plan 2 in ID = 1(HASH UNIQUE)
Does the same principle apply to ORDER BY and GROUP BY clause?
Thanks