alternative for 'Distinct'

[sandycrab]

Hi Friends,
Could anyone plz tell me what is the alternative for 'Distinct' keyword in Oracle 9i.
Thanks in advance
Sandy

[pando]

select job
from emp
group by job

[stmontgo]

here's an example.......

instead of...........

SELECT DISTINCT d.deptno ,
d.dname ,
FROM dept d ,
emp e
WHERE d.deptno = e.deptno ;

write it this way...........


SELECT d.deptno ,
d.dname
FROM dept d
WHERE EXISTS ( SELECT e.deptno
FROM emp e
WHERE d.deptno = e.deptno ) ;


and of course, don't take my word for it, generate an explain for both and compare.

I in fact today took a query on my current project and reduced cost from 123,000 to 8, the query went from 8 minutes to 25 seconds by avoiding the select distinct.

steve

[sandycrab]

Thanks Pando and Steve for valuable suggestions. I'll go with Steve. Pando, your option takes same time as distinct...but still, an alternative. Thanks a lot...
Sandy

[pando]

yes mine is exactly the same as distinct

exists would be fast if outer query returns small number of rows, I am not so sure if it´s large .....