ProductJoin join condition of ("LOGGING_KEY = ETL_TEMP_HIX_DEV.TABLE4.LOGGING_KEY"

Teradata Applications

ProductJoin join condition of ("LOGGING_KEY = ETL_TEMP_HIX_DEV.TABLE4.LOGGING_KEY"

ProductJoin join condition of ("LOGGING_KEY = ETL_TEMP_HIX_DEV.TABLE4.LOGGING_KEY"

I am getting a product join result when explaining the query below:

SELECT 1

FROM TABLE1 LLL

INNER JOIN TABLE2 CLL ON CLL.LOGGING_KEY = LLL.CSA_LOGGING_KEY

LEFT JOIN TABLE3 ABR ON ABR.SURVEY_CD = LLL.SURVEY_CD

AND ABR.TBL_NM = 'SRVY_STG'

AND ABR.CLMN_NM = 'N/A'

AND ABR.ENVRNMNT_CD = 'CSA'

LEFT JOIN TABLE4 LZ ON LLL.LOGGING_KEY = LZ.LOGGING_KEY

LEFT JOIN TABLE5 EDW ON EDW.SURE_ID = LZ.SURE_ID

AND EDW.VERSION_NUM = LZ.VERSION_NUM

AND EDW.SURE_CODE = LZ.SURE_CODE

WHERE LLL.SURVEY_CD = '1010'

AND LLL.SUBJECT = 'HRA'

AND LLL.FLOW = 'WF_LZ_LPR_CLNCLSVY'

AND LLL.PUBLISHER_IND = 'Y'

AND LZ.TRANSACTION_ID = 'I'

AND CLL.PUBLISHER_IND = 'N'

AND EDW.SURE_ID IS NULL QUALIFY ROW_NUMBER() OVER (

ORDER BY CLL.LOGGING_KEY

) = 1

GROUP BY ABR.AUDT_RULE_ID

,CLL.LOGGING_KEY

,LZ.SURE_CODE

,LZ.SURE_ID

,LZ.VERSION_NUM;

There is no error when using this code and it run successfully. I just want to eliminate the statement "join condition of ("LOGGING_KEY = ETL_TEMP_HIX_DEV.TABLE4.LOGGING_KEY")" to optimize this query. One thing I did is to delete the where condition which is not really good because we need the condition to get the exact result. What i think is to use volatile. temp or work table to hold records before joining the tables. Please advice thanks!

2 REPLIES
Enthusiast

Re: ProductJoin join condition of ("LOGGING_KEY = ETL_TEMP_HIX_DEV.TABLE4.LOGGING_KEY"

Hi,

Is it possible for you to share the explain and masking the db objects.It is all upto the optimizer to think. However, some remedial ways can be found to make it faster. It depends on many factors 

data demography, statistics etc.

Maybe we have to see also if we can nail down to a hash join by producing hash arrays.

Your statement ...join condition of.. can mean anything. It can have further  explanation like enhanced by DPE ....and so on and so forth.

Btw, are your tables partitioned?

If you are restricted not to share info, then I can suggest 

Collect fresh statistics on all required fields.

You create temporary tables and collect stats

Cheers,

Raja

Senior Apprentice

Re: ProductJoin join condition of ("LOGGING_KEY = ETL_TEMP_HIX_DEV.TABLE4.LOGGING_KEY"

A prodcut join is not bad per se, it might actually be the least expensive plan.

Check the estimated number of rows for the step preparing the join (the one with a "duplicated to all AMPs") and divide it by the number of AMPs in your sstem to get the actual number.

Additonally you might move the ROW_NUMBER into a Derived Table to do it as early as possible, e.g.

SELECT...
FROM
(
SELECT *
FROM TABLE1 LLL INNER JOIN TABLE2 CLL
ON CLL.LOGGING_KEY = LLL.CSA_LOGGING_KEY
WHERE
LLL.SURVEY_CD = '1010'
AND LLL.SUBJECT = 'HRA'
AND LLL.FLOW = 'WF_LZ_LPR_CLNCLSVY'
AND LLL.PUBLISHER_IND = 'Y'
AND CLL.PUBLISHER_IND = 'N'
QUALIFY
ROW_NUMBER() OVER ( ORDER BY CLL.LOGGING_KEY ) = 1
) as LLL
LEFT JOIN TABLE3 ABR ...

And you might rewrite the LEFT JOIN .. WHERE EDW.SURE_ID IS NULL to a NOT EXISTS, which might perform better.