Archives of the TeradataForum

Message Posted: Sat, 19 May 2007 @ 22:56:22 GMT

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

I've had a funny issue this morning where a query did a product join because some of the stats said the table was populated and some of the stats said it was empty. The stats on the column being used in the query had stats saying the table is populated - so I thought these would have been used.

The SQL:

     SELECT 2 (FORMAT'99') (CHAR(2)),
     CRS.CARD_ACCOUNT_NO (FORMAT'X(16)') (CHAR(20)),
     CRS.WINBACK_VALUE_SCORE (FORMAT'+99999') (CHAR(6)),
     CRS.CLOSURE_VALUE_SCORE (FORMAT'+99999') (CHAR(6)),
     CRS.SEGMENT_CD (FORMAT'X(1)') (CHAR(1)),
     (CASE
     WHEN (SHLS.HGH_LVL_SGM=1)
     THEN 'Y'
     ELSE 'N'
     END) (CHAR(1)),
     (CASE
     WHEN (CP.IND_NO_MKTG=1)
     THEN 'Y'
     ELSE 'N'
     END) (CHAR(1))
     FROM CC_RETENTION_SCORES_MONTH CRS
     LEFT JOIN CC_XREF_CIDPERSID CXC ON
     CRS.CARD_ACCOUNT_NO=CXC.CARD_ACCOUNT_NO
     LEFT JOIN CID_PERSONS CP ON
     ZEROIFNULL(CXC.CIDPERSID)=ZEROIFNULL(CP.CIDPERSID)
     LEFT JOIN STS_CUSTOMERS_FULL_RUN SCFR ON
     ZEROIFNULL(CXC.CIDPERSID)=ZEROIFNULL(SCFR.CIDPERSID)
     LEFT JOIN STS_HIGH_LEVEL_SEGMENT SHLS ON
     ZEROIFNULL(SCFR.CUR_SUB_SEG_CD)=ZEROIFNULL(SHLS.SUB_SGM);

Stats:

     Date       Time       Unique Values    Column Names
     07/05/09 19:41:41         5,123,073    CARD_ACCOUNT_NO
     07/05/09 19:41:52                 1    LOAD_DT
     07/05/09 19:40:40                 0    CARD_ACCOUNT_NO,MONTH_END_DT

The last stats (CARD_ACCOUNT_NO,MONTH_END_DT) were incorrect as it thought the table was empty (timing issue) and it used these over the CARD_ACCOUNT_NO (correct) stats. As it is only using CARD_ACCOUNT_NO in the query I would have though it would have used those stats and decided against the product join.

Can anyone offer any explanation as to why this would happen?

Cheers,

Martin

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