RE: Question about use of DISTINCT

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandra Becker Sent: Monday, October 17, 2011 2:49 PM
To: oracle-l
Subject: Question about use of DISTINCT

IBM zSeries
SUSE 10
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.

--

Sandy
Transzap, Inc.

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 17 2011 - 13:54:23 CDT