 |
 |
Archives of the TeradataForum
Message Posted: Tue, 21 Aug 2001 @ 08:34:28 GMT
| Subj: | | Re: Integer vs Character PI |
| |
| From: | | Dieter N�th |
| | I created 5 duplicate copies of a table with a single column primary index defined as an integer. When I join these tables together
on their common primary index, the optimizer treats everything as an ample local process (no redistributions.) | |
| | Then, I created 5 duplicate copies of a table with a single column primary index defined as a CHAR. When I join these tables
together on their common primary index, the optimizer redistributes the data after every merge join. | |
| | Can someone explain this to me? | |
I just tried this on a V2R04.00.01.45 and it showed the same behaviour. But after inserting 10000 records each, the CHAR-join were AMP-
local also. But if i mix CHARs/VARCHARs, then there's a redistribution (don't know why, shouldn't be, because the Hashing Algo treats
CHARs/VARCHARs the same)
| | I tried this on my system, and it worked just fine as either integer or char. We'll need to see your DDL. These possibilities
come to mind... | |
| | - All the character indexes must be exactly the same length -- but you've probably thought of this already. | |
NO, there's never been a difference if the length doesn't match, which release are you talking about? But joining on different domains
would be stupid anyway.
| | - When you join, are you including any literals? Character literals are stored as VARCHAR, not CHAR. For instance, if you
have a char(10) column that includes 'TAIWAN ', the following expressions: | |
| | hashrow(char10_column)
hashrow('TAIWAN ') | |
| | do NOT return the same value, because the first is fixed and the second is variable. | |
Again, which release are you talking about? HASHROW always returns the same RowHash.
Dieter
| |
