Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 15 Nov 2002 @ 11:47:41 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Rowid and hash functions
From:   Frank O Connor


We have several tables that are keyed with an integer NUPI. On occasion, we receive data where the rows cannot be keyed in our process due to, say, invalid account numbers etc.

This then would leave our nupi as null and this causes problems as it can lead to significant skewing to a single amp thus reducing the performance on the teradata system.

We then concluded that if we use negative values in this key, it can be determined that this is a null equivalent (and this is how it is shown in our user views).

We have tried coalesce(NUPI, time*-1) but we note that the time remains constant for a given batch (as if it takes the start time and uses that as a constant for the insert/select stage). Whilst this is better than nulls (skewing should reduce over time), it can slow down the insert due to the NUPI/Duplicate problems. The same applies to current_time.

Now what I plan to do is use hashbucket(hashrow(table.rowid))*-1 instead. This allows for a potential 64K values and lessens the nupi duplicate problem considerably.

Are there any considerations, reservations that I should have as regards this approach (such as will it go out of support, is it reliable and so on)

Any help will be appreciated in this regard.



  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020