Archives of the TeradataForum
Message Posted: Wed, 07 Jan 2004 @ 22:40:30 GMT
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.
|Copyright 2016 - All Rights Reserved
|Last Modified: 15 Jun 2023