Any ideas why "GROUP BY" query is faster that
"DISTINCT" in this query?
SELECT OWNER FROM DBA_OBJECTS GROUP BY OWNER;
Good Timing (GROUP BY) Plan (runs in 9 secs):
---------------------------------------------------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 457 | 22850 | 2062 (9)|
00:00:25 |
| 1 | HASH GROUP BY
| | 457 | 22850 | 2062 (9)|
00:00:25 |
|* 2 | FILTER
| | | | |
|
|* 3 | HASH JOIN
| | 506K| 24M| 2019 (7)|
00:00:25 |
| 4 | TABLE ACCESS FULL
| USER$ | 457 | 5484 | 7 (0)|
00:00:01 |
|* 5 | TABLE ACCESS FULL
| OBJ$ | 506K| 18M| 2006 (7)|
00:00:25 |
| 6 | NESTED LOOPS
| | 1 | 26 | 1 (0)|
00:00:01 |
|* 7 | INDEX RANGE SCAN
| I_OBJAUTH1 | 2 | 26 | 1 (0)|
00:00:01 |
|* 8 | FIXED TABLE FULL
| X$KZSRO | 1 | 13 | 0 (0)|
00:00:01 |
|* 9 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 10 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 11 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 12 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 13 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 14 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 15 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 16 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 17 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 18 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 19 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 20 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 21 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 22 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 23 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 24 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 25 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 26 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 27 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 28 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 29 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
| 30 | VIEW
| | 1 | 13 | 2 (0)|
00:00:01 |
| 31 | FAST DUAL
| | 1 | | 2 (0)|
00:00:01 |
|* 32 | TABLE ACCESS BY
INDEX ROWID| IND$ | 1 | 9 | 1 (0)|
00:00:01 |
|* 33 | INDEX UNIQUE SCAN
| I_IND1 | 1 | | 1 (0)|
00:00:01 |
---------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT OWNER FROM DBA_OBJECTS;
Bad Timing (DISTINCT) Plan:
--------------------------------------------------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 457 | 22850 | 2025 (8)|
00:00:25 |
| 1 | HASH UNIQUE
| | 457 | 22850 | 2025 (8)|
00:00:25 |
|* 2 | FILTER
| | | | |
|
|* 3 | HASH JOIN
| | 506K| 24M| 2019 (7)|
00:00:25 |
| 4 | TABLE ACCESS FULL
| USER$ | 457 | 5484 | 7 (0)|
00:00:01 |
|* 5 | TABLE ACCESS FULL
| OBJ$ | 506K| 18M| 2006 (7)|
00:00:25 |
|* 6 | HASH JOIN
| | 2 | 52 | 2 (50)|
00:00:01 |
|* 7 | INDEX RANGE SCAN
| I_OBJAUTH1 | 2 | 26 | 1 (0)|
00:00:01 |
| 8 | FIXED TABLE FULL
| X$KZSRO | 100 | 1300 | 0 (0)|
00:00:01 |
|* 9 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 10 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 11 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 12 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 13 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 14 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 15 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 16 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 17 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 18 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 19 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 20 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 21 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 22 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 23 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 24 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 25 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 26 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 27 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 28 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
|* 29 | FIXED TABLE FULL
| X$KZSPR | 1 | 26 | 0 (0)|
00:00:01 |
| 30 | VIEW
| | 1 | 13 | 2 (0)|
00:00:01 |
| 31 | FAST DUAL
| | 1 | | 2 (0)|
00:00:01 |
|* 32 | TABLE ACCESS BY
INDEX ROWID| IND$ | 1 | 9 | 1 (0)|
00:00:01 |
|* 33 | INDEX UNIQUE SCAN
| I_IND1 | 1 | | 1 (0)|
00:00:01 |
--------------------------------------------------------------------------------------------------------------------
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
