Just trying to understand the workings of the secondary index.
I understand the concepts of 2-AMP and All-AMPs Retrieve, I am just trying to understand how the actual secondary index subtable is stored.
Is the USI subtable stored on one AMP and one AMP only, or is it duplicated across all AMPs? I understand that the NUSI subtable is stored on all AMPs and that each AMP stores its own values, so this is an All-AMPs Retrieve as it scans all of the AMPs' subtables and then those AMPs with matching values return their rows.
The USI subtable can be stored in any number of AMP. Each AMP has its own part of USI subtable… The key here is row id will be unique in all subtable and we no need to refer all USI subtable for single row retrieval. When we performing hashing algorithm, the hash map points to only one AMP containing the subtable row corresponding to the row hash value. Then the base row is retrieved from an AMP which the subtable indicates.
The point to be noted here is the subtable AMP id differs for different row hash values.
I am sorry for giving a wrong reply. Balamurugan is correct. Each AMP will contain it's part of USI subtable. The main difference in distribution of the main table and subtable is: main table is distributed based on hash value of PI but USI subtable is distributed based on USI value.
The row hash for a secondary index is usually different from the row hash for the primary index on the same table, unique secondary indexes are generally stored on a different AMP than the row they point to. Nonunique secondary indexes are not hashed and are always stored on the same AMP as the rows they point to.
Just to add to the above comments… USI can be a 1 AMP or 2 AMP operations. i.e. typically it is a 2 AMP operation, but there is a possibility of subtable row and base table row can store in the same AMP, since both are hashed separately. In this case it is a one AMP operation.
The NUSI will always be hashed whenever it is used. Without hashing it is not possible to point to the respective Row ID in NUSI subtable in any AMP.