Information about single table join index and NUSI and USI

Database

Information about single table join index and NUSI and USI

Hi All,

1. Single table join index:- As per my understanding, single table join index gives us flexibility to change PI of the table and we use only one table i.e no join with other table is required.

My question is in Single table join index, does joing between the tables takes place or not as i have read in forum that only one table is used?? Description with example will be helpfull for me.

2. In USI and NUSI, can both table (base and sub table) reside in same amp. If yes, how??

3. Since we don't have dense rank function available in Teradata, what is its alternative??

I have referred earlier posts but i'm not able to understand above questions clearly.

Many thanks in advance,

Rahul

1 REPLY
Enthusiast

Re: Information about single table join index and NUSI and USI

A column subset of a very large base table defined with a NUPI defined on a join key that causes its rows to be hashed to the same AMP as another very large table with a primary index defined on that same join key and to which it is frequently joined.Useful for resolving joins on large tables without having to redistribute the joined rows across the AMPs.

you cannot query or update them directly STJI.

USI access is usually a two-AMP operation because Teradata Database typically distributes a USI row to a different AMP than the base table row the index points to. If the system distributes the USI subtable row to the same AMP as the base table row it points to, then only one AMP is accessed. You know NUSI .

Dense_rank is very much there in 14.