-
Hi Friends,
Could anyone plz tell me what is the alternative for 'Distinct' keyword in Oracle 9i.
Thanks in advance
Sandy
"Greatest Rewards come only with Greatest Commitments!"
-
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
I'm stmontgo and I approve of this message
-
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
"Greatest Rewards come only with Greatest Commitments!"
-
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 .....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|