Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sat, 20 Apr 2002 @ 23:57:51 GMT

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

Subj:   Re: Nulls in a composite Primary Key deployed as a Primary Index
From:   John K. Wight

Null's have affect on the hash if the column the system is hashing on (tables or spool) is the only column in the PI (if part of multi- column PI - it s diluted with the other column vales - unless they are NULL too). This is usually more prevalent on tables where the data is badly skewed with NULL values and it is being used to joined to another table and the optimizer uses that column to hash on for the join. You often see spool space being blown because all the NULL rows will go to one AMP (HOT AMP Alert!!!).

At a recent customer, this was quite prevalent at times and qualifying IS NOT NULL allowed the spool to be created OK and the query to run well. HOWEVER - as Pat Bell indicated in a previous note, this complicated the SQL and END USERS would have to know this in all there queries where joining in these conditions. A redesign of the denormalized tables into associative tables would address the situation. Here is yet another case for normalized structures - you can reduce the occurrences of lots of NULLs!

Just my opinion for what it is worth on today's market.

  <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: 28 Jun 2020