This discussion is archived
10 Replies Latest reply: Jul 28, 2013 11:27 AM by SomeoneElse RSS

Difference between UNIQUE  and DISTINCT

617414 Newbie
Currently Being Moderated
Please help me with this confusion .

What is the difference between the following queries
Select UNIQUE(c1),c2,c3 from t1;
Select DISTINCT c1,c2,c3 from t1;
Select c1,DISTINCT c2,c3 from t1;
Does the above queries return :
1. Unique values for c1 and the corresponding min(ROWNUM) values for c2,c3.
2. Distinct combination of values for c1,c2,c3.
3. Distinct combination of values for c2,c3 and the correponding min(ROWNUM)
values for c1.

Please reply
  • 1. Re: Difference between UNIQUE  and DISTINCT
    John Spencer Oracle ACE
    Currently Being Moderated
    Well, your thirs query is not syntactically valid since DISTINCT needs to follow immediately after the select.

    Your first two are equivalent since UNIQUE and DISTINCT are synonyms. Please note that neither UNIQUE nor DISTINCT are functions, so the parens, although allowed, are meaningless. DISTINCT applies to all columns in the result set.

    So, the two that are syntactically valid will return the distinct combinations of c1, c2, and c3. Since rownum is assigned when a row is chosen for output, there is no concept of MIN(rownum) at all. If you are refering to rowid, then there will never be a rowid associated with the distinct because if you include the rowid in the result set, it will become one of the columns that are distincted and, since no two rows have the same rowid, you would get all of the rows.

    John
  • 2. Re: Difference between UNIQUE  and DISTINCT
    SusheelSingh Newbie
    Currently Being Moderated
    There is no difference in Unique and Distinct so it will fetch distinct row from your
    query

    and third one is wrong becuase Distinct is use just after selece statement.
  • 3. Re: Difference between UNIQUE  and DISTINCT
    617414 Newbie
    Currently Being Moderated
    thanks a lot john .
    got your point.

    By min(rownum) ,
    i meant the values of the other columns that are encountered with the first occurrence of the distinct values of c1 in query 1 and 3.

    But , if unique and distinct are applied to combination of all the columns in the
    query , then it makes no sense.

    Thank you
  • 4. Re: Difference between UNIQUE  and DISTINCT
    605183 Newbie
    Currently Being Moderated
    i have marked that... Using Unique keyword is more faster than Distinct keyword.... So use Unique Keyword.

    Bhavin
  • 5. Re: Difference between UNIQUE  and DISTINCT
    BluShadow Guru Moderator
    Currently Being Moderated
    user602180 wrote:
    i have marked that... Using Unique keyword is more faster than Distinct keyword.... So use Unique Keyword.

    Bhavin
    Would you care to show us the proof of that with examples?
  • 6. Re: Difference between UNIQUE  and DISTINCT
    Karthick_Arp Guru
    Currently Being Moderated
    From the document.

    DISTINCT | UNIQUE

    Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows
    selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.

    Distinct and Unique are just synonymous so i dont see there will be any performance difference. Do you have any test case witht you. If so do share it.

    Thanks,
    Karthick.
  • 7. Re: Difference between UNIQUE  and DISTINCT
    469223 Newbie
    Currently Being Moderated
    BluShadow wrote:
    user602180 wrote:
    i have marked that... Using Unique keyword is more faster than Distinct keyword.... So use Unique Keyword.

    Bhavin
    Would you care to show us the proof of that with examples?
    u-n-i-q-u-e -> 6 letters
    d-i-s-t-i-n-c-t -> 8 letters

    Clearly, 6 letters is "more faster" than 8.
  • 8. Re: Difference between UNIQUE  and DISTINCT
    739894 Newbie
    Currently Being Moderated
    just one problem .. if both unique and distinct are the same, then why do we need two keywords for doing the same thing ????
  • 9. Re: Difference between UNIQUE  and DISTINCT
    838a02c8-46f5-4528-9504-0fe20ed690f3 Newbie
    Currently Being Moderated

    This can be done by using
    SET AUTOTRACE ON EXPLAIN
    But on doing so i didn't found any difference !!
    Here is the details :

     

     

    SQL> SELECT DISTINCT DEPTNO FROM EMP;

     

     

        DEPTNO

    ----------

            30

            20

            10

     

     

     

     

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 3709190377

     

     

    ---------------------------------------------------------------------------

    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

    ---------------------------------------------------------------------------

    |   0 | SELECT STATEMENT   |      |     3 |     9 |     4  (25)| 00:00:01 |

    |   1 |  HASH UNIQUE       |      |     3 |     9 |     4  (25)| 00:00:01 |

    |   2 |   TABLE ACCESS FULL| EMP  |    15 |    45 |     3   (0)| 00:00:01 |

    ---------------------------------------------------------------------------

     

     

     

     

    SQL> SELECT DISTINCT DEPTNO FROM EMP;

     

     

        DEPTNO

    ----------

            30

            20

            10

     

     

     

     

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 3709190377

     

     

    ---------------------------------------------------------------------------

    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

    ---------------------------------------------------------------------------

    |   0 | SELECT STATEMENT   |      |     3 |     9 |     4  (25)| 00:00:01 |

    |   1 |  HASH UNIQUE       |      |     3 |     9 |     4  (25)| 00:00:01 |

    |   2 |   TABLE ACCESS FULL| EMP  |    15 |    45 |     3   (0)| 00:00:01 |

    ---------------------------------------------------------------------------

  • 10. Re: Difference between UNIQUE  and DISTINCT
    SomeoneElse Guru
    Currently Being Moderated

    Before you reply, please note the age of the thread.