query not running as per the estimated time in its explain plan
I have 2 queries both are same, but one is optimised and the other is not. I am judging that a query is optimised on the basis of the Total estimation time the explain plan gives. The optimised one has less estimation time...but yes it has 1 extra steps in the explain plan as compared to the explain plan of the not-optimised query. But the whole issue is, the query which is not -optimised runs faster as compared to the optimized query. As per the explain plan the optimised query should run .28 seconds and the non-optimised should take .63 seconds. But when i run the query the non-optimised takes 2 min and the optimised one takes 4 mins.
Can you help me, what could be the reason behind this.
Re: query not running as per the estimated time in its explain plan
The explain plan generated by optimiser is completely dependent upon the statistics provided to it. Otherwise it will take a sample data to come up with a explain plan, which might not be that accurate. Moreover the estimation given is based on the condition that there is no other load on TD system while execution of this query. So to bank upon the explain plan you have to enure that you are providing proper statistics over data. Hope it helps.