Archives of the TeradataForum

Message Posted: Mon, 30 Oct 2006 @ 17:48:57 GMT

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

It's common knowledge that RDBMS engines will NOT use any indexes when functions have been invoked. Joining on both sides of a function clause:

     "(TRIM('BC04'||'01'||TRIM(STG1.BCF_LAST_RATE_CODE)||TRIM(STG1.BCF_LAST_RATE_CODE_SEQ )||'2') =
     TRIM(STG2.BC04_TABLE_IDENT)||TRIM(STG2.BC04_REC_KEY_SUB)||TRIM(STG2.BC04_REC_NO))
     AND (TRIM('BC37'||'01'||TRIM(STG2.BC04_VARIABLE)||'1') = TRIM(STG3.BC37_TABLE_IDENTY
     )||TRIM(STG3.BC37_SUB_KEY1)||TRIM(STG3.BC37_RATE_CODE)||TRIM(STG3.BC37_SUB_KEY2))"

Will cause severe slow-down, I would suggest creating a computed field on load, and in the target - then creating a NUSI on both columns, this should fix the performance issue.

The other piece to this is:

     " AND (STG.BCM_ACCOUNT_NO1 = substr(AGR.Account_Num,1,16)"

You need a secondary computed field on AGR.account_num - I'm guessing this is unique? In any event, with large sets of data, functions within equal clauses often cause unwanted scans of information, rather than utilizing indexes, or even compression within the column.

This results in severe slow down.

Cheers,

Dan Linstedt

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