Archives of the TeradataForum

Message Posted: Wed, 07 Sep 2005 @ 21:12:23 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi folks,

I wonder if anyone can shed any light on the following. I know I haven't included all the information here, but if anyone has come across the following and solved/resolved it I'd very much appreciate having your experience.

We have a query which joins about 8 or 9 tables. All of the joins are INNER joins. We are currently on V2R5.0.3.

If we change the order of two of the tables in the FROM clause the query plan changes (hence the comment about urban myths !) and although the change is subtle, the resulting run time is vastly different. We have explained both queries from different sessions to try and eliminate any random amp samples causing the join plan to change. Our real problem comes about because the sql is generated by a tool and so we can't gaurantee to get it generated the 'good' way all the time. Even if we could, I don't believe that this is a problem caused by the sql that has been generated.

I have seen numerous discussions, email conversations, comments about this happening over the years and (to be honest) usually ignored it. However I've now seen this for myself and so can't dismiss it anymore.

As far as I'm concerned this has to be a bug. From a logical (relational) perspective, I don't believe that changing the order of tables in a query which only contains INNER joins shouldn't change the explain plan. We're going to talk to our NCR contact about this.

Has anyone else come across this ?

Did you manage to work out what was going wrong with the sql ?

And how did you solve / resolve it ?

Cheers,

Dave

Ward Analytics Ltd: Information in motion
(www.ward-analytics.com)

	<Prev	Next>		<<First	<Prev	Next>	Last>>