It seems that for a NoPI table , a query id is submitted to a randomized hashing algorithm to produce a hash value comprising of hash bucket number and a remainder. This number is used to assign a row into an amp. Once it reaches an amp, the amp software in turn generates a different hash bucket number and treats it as a RowID.
If it is so, then how does it do a lookup to the hashmap and it is more time-consuming than a normal PI.
Can anyone please throw some light upon this for me?
Thanks and regards,
If I'm not mistaken; when doing an insert in a NoPI table, the hashed query-id is only used to target an AMP (and be random in doing so) for the write. Following that, the AMP itself will determine the RowID based on a hash-bucket associated to it, and a 44 bits uniqueness ID.
(Again - if I'm correct) the hash map would only be used for a NoPI-table-lookup, if a USI is additionaly implemented (which leads to a sequential 2-AMP-operation, and hence is more time-consuming than the 1-AMP-operation a PI-lookup is). With a NUSI, the lookup would be an all-AMP-operation, as is a tablescan when no seconday indexes are used at all; however parallel in both cases, I would say the AMP's themselves have more work to do (= more time-consuming) than when only a row has to be looked up based on a PI/row hash.
A Teradata-guru should comment on the above.
(Without pretending to be a 'Teradata guru'...)
In the original post the question was "If it is so, then how does it do a lookup to the hashmap and it is more time-consuming than a normal PI."
- With a NoPI table and no other indexes on it, there is no 'hashmap lookup' processing. Processing against the NoPI table is usually an all-AMP, full table scan.
- As soon as you put a USI or NUSI on the table and the index is used then processing against the table is exactly the same as for a PI table when accessed via a SI. The access is either two-AMP (USI) or all-AMPs (NUSI) because each type of SI carries the rowid of the base row - and the rowid is a unique pointer/reference to the row.
I usually consider a 'lookup' of a NOPI table to be more time-consuming than a lookup from a PI table because of the above processing.