I wrote a query with join and same one with the subquery.
When I did explain plan it surprised me that the subquery was faster than the join. I was expecting it other way.
Does any one know why the subquery ran faster than the join query.
I had index on AuthAttr_Res_ID ,AuthAttr_Work_ID

e.g

SQL> Select a.Res_ID
2 from mwebRes a, MwebAuthAttrib b, mwebwork c
3 where a.Res_Status <> 20
AND a.Res_Entity_Type = 3
4 AND a.Res_ID =b.AuthAttr_Res_ID
5 AND b.AuthAttr_Work_ID =c.Work_ID
6 AND c.Work_Entity_Type = 4 AND c.Work_Status <> 40 group by a.res_id ;

----------------------
SQL> Select Res_ID
2 from mwebRes
3 where Res_Status <> 20 AND Res_Entity_Type = 3
4 AND Res_ID IN (Select AuthAttr_Res_ID
5 From MwebAuthAttrib
6 where AuthAttr_Work_ID IN
7 (Select Work_ID
8 From MwebWork
9 where Work_Entity_Type = 4 AND Work_Status <> 40));