Distinct elimination (Oracle Database 10g to 11g Change)

posted Sep 24, 2010, 9:12 AM by Sachchida Ojha

Starting with Oracle Database 11g optimizer will go through the query blocks of a select statement and check if distinct can be removed
from the query block, it will remove the distinct if the query block is guaranteed to return distinct rows without it. This feature is applicable only for SELECT statements. If distinct has been eliminated by optimizer than you will not see steps like HASH UNIQUE / SORT UNIQUE in the explain plan.

Example

select distinct empno from emp;
11.1.0.7.0 Execution Plan

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    14 |    56 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

 
10.2.0.4 Execution Plan

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    14 |    56 |     2  (50)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT|        |    14 |    56 |     2  (50)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
Comments