Archives of the TeradataForum

Message Posted: Wed, 07 Jan 2004 @ 22:40:30 GMT

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

You've got that right, Dave. As long as we're dealing only with inner joins, the optimizer should be free to choose any join order it wants. This is a GOOD THING because it gives the optimizer more options.

Unfortunately, it doesn't quite work that way. If you code your request using joined table syntax (what most of the world calls ansi syntax) the optimizer may not choose the same plan it would choose if regular (old fashioned?) syntax was used.

This is the bug Teradata plans to fix in 5.1 and 6.0. Until that time, it might be a good idea to revert to regular syntax. Like so:

FROM OBC_MASTER.LU_OBC_RCONTACT_JOB_ADDNL_INFO REC
    ,OBC_MASTER.LU_OBC_JOB_INFO                RECJOB
    ,OBC_MASTER.FA_OBC_MDN_MTD                 RECMTD
    ,OBC_LOAD.NPA_NXX_OUTBOUND                 L

WHERE RECJOB.JOB_ID         = REC.JOB_ID
 AND  RECMTD.JOB_ID         = REC.JOB_ID
 AND  L.NPANXXX             = RECMTD.NPANXXX
 AND  RECJOB.CONTACT_TYPE   = 'R'
 AND  REC.ORIG_CONTACT_TYPE = 'P'

Among other things, this is MUCH easier to read.

There is a transitive term to be derived here:

RECJOB.JOB_ID = RECMTD.JOB_ID

This term would allow the optimizer to consider joining RECJOB to RECMTD. That may or may not be a wonderful thing, but as the request was originally coded, that join would not even be considered. When the request is re-coded as above, the join would be considered.

I would still strongly recommend that Ganga get the explains for his queries. It takes practice and knowledge to make use of explains but it's a lot better to know what plan the optimizer generated than having to guess at it.

Jon

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