It appears the database recognizes the uniqueness and throws out the DISTINCT. The explain plan is not showing a hash unique in either case. Thanks, Mark. Sandy On Mon, Oct 17, 2011 at 12:54 PM, Powell, Mark <mark.powell2@xxxxxx> wrote: > > 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 > 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. > > > -- > //www.freelists.org/webpage/oracle-l > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Sandy Transzap, Inc. -- //www.freelists.org/webpage/oracle-l