Hi Friends,
Could anyone plz tell me what is the alternative for 'Distinct' keyword in Oracle 9i.
Thanks in advance
Sandy
Printable View
Hi Friends,
Could anyone plz tell me what is the alternative for 'Distinct' keyword in Oracle 9i.
Thanks in advance
Sandy
select job
from emp
group by job
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
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
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 .....