Archives of the TeradataForum
Message Posted: Sat, 20 Apr 2002 @ 23:57:51 GMT
| 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.
| 
