Archives of the TeradataForum
Message Posted: Sat, 19 May 2007 @ 22:56:22 GMT
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.
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);
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|