Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Sunposis and long running query
 
From:   Dan Linstedt

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023