Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DISTINCT join vs. EXISTS (correlated subquery)

WestDraytonOct 11 2010 — edited Oct 11 2010
I found a web page that says following:
Oracle SQL Tuning - DISTINCT vs. EXISTS:
Avoid joins that require the DISTINCT qualifier on the SELECT list in queries which are used to determine information at the owner end of a one-to-many relationship. The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate values. EXISTS is a faster alternative, because the Oracle optimizer realizes when the subquery has been satisfied once, there is no need to proceed further and the next matching row can be fetched. Below query returns all department numbers and names which have at least one employee. SELECT DISTINCT dept_no, dept_name FROM dept D, emp E WHERE D.dept_no = E.dept_no; SELECT dept_no, dept_name FROM dept D WHERE EXISTS ( SELECT 'X' FROM emp E WHERE E.dept_no = D.dept_no);
( http://askanantha.blogspot.com/2007/10/sql-tuning-guidelines-for-oracle-simple.html )

What do you think of such rule, said out, that "distinct" with joins is bad, and "exists" is better?

I understand that it depends of data amounts between tables, and the health of indexes on tables. So that "distinct join" may become cheaper for some databases. Or can we generalize ar the statement was done still?
This post has been answered by Frank Kulash on Oct 11 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 8 2010
Added on Oct 11 2010
3 comments
5,348 views