This has confused me, what I observed is the query with IN subquery (without group by, distinct ) was faster than the join with group by or distinct when I was expecting reverse.
As you said I should have distinct inside IN for subquery for making it more fast ( thanks for this tip).
But that was not the point the subquery without the distinct is also faster than the join. I have always read that one should use join and not the subquery whenever possible so I was thinking that join would be faster !!!


BTW Tamilselvan> I am writing this on the basis of EXPLAIN PLAN.