Hi, I have a select statement in which I use table variable(with a PK) inner Join a table inner join a materialized view inner join a table left join a table left join a table.I select atleast 2 - 3 columns from each of the objects involved. The Tables which are involved in the left join have a one to many relation that means it increases my row count. My concern is that if I remove the left join part and its respective column references from the select statement, it returns the rows( 25000+) in 45 secs. If I run it with all the joins it returns the rows(45000+) in 7 or 8 secs. I'm unable to understand this behaviour because in second case my row count increases as well as my no of columns, so how am I getting the result set much much faster. I have tried doing the same after clearing all the cached plans, but in vain.
Do you have any where clause in yur query? Anyway, It is better to submit both queries and the execution plans.