it is still not the right way. You meant to say A UNION B... that is what you want.

... i still wonder what might be the internal algorithm behind a distinct
clause. i feel a distinct clause along with an order by should work faster than
a distinct clause without order by. ...

why? You don't have to SORT to DISTINCT.


... This ways its easy to
delete duplicates.
....

not all of the time. Sometimes YES, sometimes NO.


We can distinct using sorting
We can distinct using hashing

ops$tkyte%ORA10GR2> set autotrace on
ops$tkyte%ORA10GR2> select distinct job from scott.emp;

JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST


Execution Plan
----------------------------------------------------------
Plan hash value: 3709190377

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    40 |     3  (34)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |     5 |    40 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   112 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

why bother sorting the data at all - that would be an unnecessary step. However, if you ask for the data to be ordered:

ops$tkyte%ORA10GR2> select distinct job from scott.emp order by 1;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN


Execution Plan
----------------------------------------------------------
Plan hash value: 596748738

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    40 |     4  (50)| 00:00:01 |
|   1 |  SORT UNIQUE       |      |     5 |    40 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   112 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

then we will do the two operations in a single pass - we'll sort and distinct at the same time - but since sorting is very expensive - if we don't have to sort - we won't.

no, it has never had to be true that either did group by.

NEVER

if you need data sorted - you need to use ORDER BY, if the optimizer can skip doing a sort of the data by including it in some other step - fine, but YOU cannot skip putting the order by there.


http://asktom.oracle.com/Misc/order-in-court.html



what the trainer should have said is "Oracle is smart enough to skip ordering the data if the prior rowsource is already returning the data sorted, but you must use order by in all cases if you want it sorted. The optimizer will remove it if it can - but you cannot"

... Most of the time when sort is done on numbers, dates or strings, we expect the
output to be ordered because those datatypes have a natural order.
...

actually - no. Consider that here in the US - we use all of 7bits to store our string data. In France, you use 8bits. Now, in the US a string sorted in binary fashion (what a group by does if it orders) would 'sort' strings correctly - since all of our letters are ascii 127 and under. The strings in France however, if sorted in binary fashion, would be *wrong*, not sorted - because you need a character set sort - so that some characters with codes between 128 and 255 sort below other characters in the 0..127 range.


so, the sort that might be done by a group by or distinct would not ORDER the data (we do a binary sort for those operations, order by uses a character set ordering to sort data)


but yes, I see your point in the differentiation - that is a good way of putting it - but use the string example instead of colors - it is more "natural" (everyone has a frame of reference for it) and we don't really order by color in the database - but we do order strings.

and it did change dramatically :)

it rarely returns data "sorted" by anything.

And it never had to, it never "always did a sort", there were many ways to distinct data from the very beginning.

...DISTINCT was practically officially recommended ....

never in any documentation I've ever read

distinct

a) never had to sort
b) sorted binary
c) if the subquery contained more than one column - what would the set be ordered by exactly??

... clearly remember an article in Oracle magazine. ...

Ahh, reminds me of how http://asktom.oracle.com/Misc/birth-of-asktom.html asktom got started in the first place, this is all the result of a "column in that magazine"</a>

... but as far I can remember the caveat "it may change in the future" was present! ...

it had already changed! before 7.1. distinct *never had to sort*, it might have, but it did not have to and a plan change could change the output at any time.

... I'd
probably order them by suit and then count each suit ...

Actually, good example. How do you "order by suit", which comes first?

We hash them - if the first card is a heart, we start a heart pile and then a clubs and then a spade and then a diamond. Maybe next time is it clubs, spades, diamonds hearts. And then we hash the rest of the deck.

We do that all of the time to separate things, to group them. We rarely "sort" them.


today - given fast cpu and large memory, we tend to use hashing. In the past, given slow cpu and small memory - we used sorting and paging.

but I don't even see how it would be

select distinct a,b,c from t

how would that have returned the data - sorted by what?


...
>b) sorted binary
Not important for numeric columns (Top-N).
.....

how could you do a top-n since you didn't know "how" it would be sorted - multiple columns, how would the sorted data be returned???

... just said that it was
often recommended by "experts" - including Oracle own magazine (and it was an
article, not just user supplied tip) - at that time as a way to sort results of
the subselect. BTW, hash joins has n...

and they were mistaken and I would have shouted as loud as possible at them to "stop it". I cannot find it (google destroyed the newsgroup archives when they bought dejanews - searching back in time is useless) but I remember quite a heated discussion with a guy named 'keystroke' in the mid 1990's. the gist was:


poster asks: how to create a view with an order by
tom said: you cannot (it was not a capability at that time)
keystroke says: when people say you cannot it means they do not know how, you use group by and group by will sort the data

tom said: bzzzztttt - totally wrong, here are a couple of examples in version 7.0 showing group by does not sort. Simply adding an index for example would change the sort order. simply changing your nls_settings would change the sort order (give the wrong sort order and so on)


because it was in an Oracle Magazine article (which are mostly written by other people, especially back then, not Oracle itself - and reviewed by copy editors - not tech editors necessarily (that is why I got involved in the first place))



... BTW, hash joins has not existed at that time, they were added
later (if I remember properly). ...

yes, that was a version 7.3 addition - the hash join was.

but even before hashing - there were indexes, unique indexes in particular and they would return data sorted in the order of the indexed columns which might not be the same as the projected columns

ops$tkyte%ORA11GR2> create table t as select rownum x, lpad(rownum,100,'0') y from dual;

Table created.

ops$tkyte%ORA11GR2> select distinct x || '-' || y from (select * from t) where x = 1 order by x;

X||'-'||Y
----------------------------------------------------------------------------------------------------
1-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
01


ops$tkyte%ORA11GR2>  select distinct x || '-' || y from (select * from t where x = 1) order by x;

X||'-'||Y
----------------------------------------------------------------------------------------------------
1-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
01

care to mention a version? I cannot reproduce