Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Chris.

Asked: April 09, 2007 - 2:29 pm UTC

Last updated: May 22, 2013 - 1:42 pm UTC

Version: 10.2.01

Viewed 100K+ times! This question is

You Asked

Can you please settle an argument we are having re: "select unique" vs. "select distinct"? The Oracle docs say they are synonymous, but it seems to imply that "distinct" forces a sort where "unique" does not. In that case they aren't synonymous and "unique" would be wrong if the input weren't already sorted by a subquery wouldn't it?

Taking any given select and changing "distinct" to "unique" seems to give the same explain plan which would support them being synonymous, but the documentation is unclear. Maybe it was version specific and is no long meaningful. I think I remember seeing somewhere that distinct was supported prior to 8.1.5 or something like that.

This all started when I was told that "for more than one column, unique is always better". Being a Tom Kyte evangelist I said "Based on what evidence? Show me."

and we said...

neither will force a sort - they are the same.

distinct will not necessarily sort.
unique will not either.


ops$tkyte%ORA10GR2> select distinct ename from scott.emp;

ENAME
----------
ALLEN
JONES
FORD
CLARK
MILLER
SMITH
WARD
MARTIN
SCOTT
TURNER
ADAMS
BLAKE
KING
JAMES

14 rows selected.

ops$tkyte%ORA10GR2> select unique ename from scott.emp;

ENAME
----------
ALLEN
JONES
FORD
CLARK
MILLER
SMITH
WARD
MARTIN
SCOTT
TURNER
ADAMS
BLAKE
KING
JAMES

14 rows selected.


Yes, in older releases - they may well sort, but by default in 10g, they'll hash instead.




Unique and distinct have been around for many many releases.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

10g R1 or R2

Cristian Cudizio, April 11, 2007 - 11:23 am UTC

i've tried on my databases, what you say is true for 10gR2, with 10.1.0.5 on Linux 32 bit oracle still sorts
SQL> set autotrace on
SQL> select distinct xy from tablea;

xy
----------------------------------------
AGENTE
AMMINISTRATORE
BACK OFFICE
CALL CENTER MANAGER
OPERATORE
OPERATORE Q.C.
RESPONSABILE Q.C.
SUPERVISORE JUNIOR
SUPERVISORE SENIOR
contact

10 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=10 Bytes=
150)

1 0 SORT (UNIQUE) (Cost=4 Card=10 Bytes=150)
2 1 TABLE ACCESS (FULL) OF 'tablea' (TABLE) (Cost=3 Card=10
Bytes=150)

While on 10.2.0.2 is

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 98 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 7 | 98 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| tablea | 7 | 98 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Tom Kyte
April 11, 2007 - 11:56 am UTC

it does not matter if oracle APPEARS TO SORT

you better do order by if you want sorted data

(because we do a BINARY SORT, not a character set sort..... when and if we use a sort to distinct)

also, partitioning, parallel and the use of many other features will defeat that.

APPEARS TO SORT

Duke Ganote, April 11, 2007 - 4:38 pm UTC

How many times have we seen this issue? "Everytime I've tried a GROUP BY (or DISTINCT or whatever), it appears to sort like an ORDER BY" sigh...
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:74320098178823#74401237604050

without using unique and distinct select unique records

A reader, May 21, 2013 - 6:52 am UTC

Hi Tom,

Hope you are fine.

I was trying to select unique records without using unique and distinct and found long back one logic posted in your site as below
############
select * from t union select * from t; (which is not efficient ).
############
But here I have modified with some little logic and fuond from the plan its ok and tested too .

##########my code
create table t(a number, b number);
select * from t union select null,null from dual where 1=2;
##########
I think its ok but need your view on that :).

thanks,
Reader
Tom Kyte
May 21, 2013 - 2:47 pm UTC

what is the point????

sure you can use a union to get a distinct - but - WHY??????

short answer, yes, union with dual would work.

but WHY??? it would not be smart. it would not be clear. it would be confusing at best.



cont to my last post.

A reader, May 22, 2013 - 5:23 am UTC

Hi Tom,

I also dont like to use union+dual for selecting unique record.

One of my coworker told me that he was asked (with out using unique and distinct) in an interview , I told him this answer. But it is funny to listen those kind of question.

But thanks for your reply :).

regards,
Reader


Tom Kyte
May 22, 2013 - 1:42 pm UTC

you could use group by as well, that is probably what they were looking for...

select a,b,c from t group by a,b,c;

but the answer with union+dual would probably "impress" them. But I'd be suspicious of them - since this is nothing I'd want a programmer doing anyway :)

In fact, I'm suspicious of queries that need a distinct in general, I always look at them to see if they are even correct to begin with! I don't know how many times the distinct was put there to "fix" a query that was returning duplicates (because of a missing join condition...)

cont to my last post.

A reader, May 23, 2013 - 5:09 am UTC

Hi Tom,

Even in that question group by was included :).

And your last line is really impressive as it happens in real time scenario :).


long live.

unique distinct

WhatsTheFussAbout, May 23, 2013 - 3:15 pm UTC

Use the ORDER BY clause to order rows, and assume there is no reliable ordering when the ORDER BY clause is not present. SQL class 101. Case closed.