Re: Question about use of DISTINCT
Date: Mon, 17 Oct 2011 13:11:33 -0600
Message-ID: <CAJzM94B=8G4Qk36FGq6xVeM1gTDAeA6CB8EDMCNk4C-+x6v0hw_at_mail.gmail.com>
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_at_hp.com> 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_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
>
>
>
-- Sandy Transzap, Inc. -- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 17 2011 - 14:11:33 CDT