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