-
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));
Sonali
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
|