Hash generation for secondary indexes

Database
Enthusiast

Hash generation for secondary indexes

My question is slightly trivial.
what is the result of the hashing algorithm for a Secondary index. The secondary index subtable contains 2 row ids. and based upon the value we first go to the subtable from where we get the rowid for the actual row. My question is when the hashing algorithm generates the hash map based on a USI, what value gets generated??? Also what do these 2 rowids signify???
3 REPLIES
Enthusiast

Re: Hash generation for secondary indexes

Hi Anadi,

Suppose you have a table with primary index defined on it...
Later you execute
create unique index(a) on tablename; ---thus creating a secondary index on table
It is the responsibility of AMP holding that column (a) to hash the value and get row hash...
After that row hash points to new AMP it belongs to by looking into hash map .
rowhash,value,PIrowid are picked and bunched together ,send via BYNET to new AMP where the new AMP provides them with uniqueness value (thus making rowid )before inserting the row into block....

I urge Supreme being to add something if missing....
Enthusiast

Re: Hash generation for secondary indexes

just adding to the above question.....how is it ensured that the subtable row and the actual row land on the same amp in the case of a NUSI......
Enthusiast

Re: Hash generation for secondary indexes

NUSI subtable rows are not distributed across the system via AMP number in the Hash Map. NUSI subtable rows are built from the base table rows found on that particular AMP and refer only to the base rows of that AMP.

Also USI is used for providing alternate access path...
While NUSI is used to prevent full table scans,value ordered NUSI is used in replacement of PPI for range related queries,NUSI created on low selective columns so that when they are used together in where clause...using BSMS are made highly selective.....