RE: Question about use of DISTINCT

  • From: "Powell, Mark" <mark.powell2@xxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 17 Oct 2011 19:54:23 +0100

You would need to look at the explain plan for the specific query in question 
to tell if the CBO recognized that the result set was distinct and did not 
require a sort unique operation to filter out duplicates.

As a general rule unnecessary clauses should not appear in your SQL since at 
best the clauses are filtered out by the optimizer and at worst cause extra 
work to be performed.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Sandra Becker
Sent: Monday, October 17, 2011 2:49 PM
To: oracle-l
Subject: Question about use of DISTINCT

IBM zSeries
Oracle EE 10.2
A developer recently put some code into production that I did not get to see 
until 3 days later when it was failing part way through and causing problems 
with the other part.  One of the things I noticed was the use of DISTINCT on 
columns that are already unique.  Because of the predicates and the way the 
primary keys were defined, they will never use the primary key index or any 
other unique index on the tables.

Question:  Is using the DISTINCT on a column that is already unique causing 
unnecessary overhead or is it just superfluous and the database is smart enough 
to ignore it?  I've run a few explain plans and the use of the DISTINCT does 
not change the explain plan.

Transzap, Inc.



Other related posts: