FAQ
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.

Search Discussions

  • Powell, Mark at Oct 17, 2011 at 6:55 pm
    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@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
  • Sandra Becker at Oct 17, 2011 at 7:13 pm
    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 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@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-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 17, '11 at 6:51p
activeOct 17, '11 at 7:13p
posts3
users2
websiteoracle.com

2 users in discussion

Sandra Becker: 2 posts Powell, Mark: 1 post

People

Translate

site design / logo © 2022 Grokbase