Index Cocepts in Teradata

Database
Highlighted

Index Cocepts in Teradata

Hi All,
Am little bit confused with the Indexes concepts in Teradata. Can any one please explain me with an example about the

->Unique Primary Index
->Non-Unique Primary Index
->Unique-Secondary Index.
->Non-Unique Secondary Index.

Thanks in Advance
Ganesh.J
10 REPLIES
Enthusiast

Re: Index Cocepts in Teradata

UPI, NUPI:
Teradata uses primary index to distribute data across AMPs. PI access is always one amp operation as teradata knows exactly where the record is. So both UPI, NUPI results in one amp operation.

Where clause on PI with UPI may return 0 to 1 record
Where clause on PI with NUPI may return 0 to many records.

USI, NUSI:
Secondary index provides an alternate path to access data. Index creates sub table on all AMPs. It stores index value, index rowhash, rowid of record. So when ever you insert data into a table having index, DB also makes corresponding entries into index sub tables.

USI is 2 AMP operation. First teradata hashes index value, goes to a particular AMP, looks for index rowhash, Then gets the index value and also rowid where record is present. Its definitely much faster than doing Full table scans.

NUSI is all AMP operation but not full table scan. In case of NUSI, each index sub table stores only its values. So if you limit on a NUSI column, Each AMP will search for the limit you apply and returns records if present.

Hope this helps.

Re: Index Cocepts in Teradata

Thanks a lot Ravi. Your explanation is simply superb.
N/A

Re: Index Cocepts in Teradata

If primary index and secondary index is in same amp than how can we tell it as two amp operation....?


Enthusiast

Re: Index Cocepts in Teradata

Its the same reason as explained in the thread above. Which part you don't understand?


Enthusiast

Re: Index Cocepts in Teradata


USI retrieval can be a 1 or 2 AMP operation.


In a scenario where the hash value of PI and USI are the same, the SI subatble row as well as the base table row will be on the same amp.


Still it is a 2 step operation, since there is a Lookup on the USI subtable & then use RowId to Lookup the basetable. But it maybe called a "1 AMP" operation



 


s_1
Enthusiast

Re: Index Cocepts in Teradata

Hi all,

 

1.  Teradata which join is better ?


2. Default teradata which join taken ?


 


Ratnam


Enthusiast

Re: Index Cocepts in Teradata

Hi Ratnam,


May I know the joins you want to compare?


Teradata has different join processing techniques chosen by the optimzer at run time based upon the multiple factors.


These factors can be table size, number of estimated rows, stats, and the use of indexes etc.


 


Khurram
Enthusiast

Re: Index Cocepts in Teradata

Hi,


Which one :



Inner join, outer join(left, right,full), cross join or product join


or are you talking about these JIs?


Join indexes(Single table join index,multitables join index,aggregate join index,sparse join index)


 


or in the context of  merge join or hash join or nested join.


 


All joins are coded for  a purpose to suit data demography, being placed at the discretion of model that serve a business functionality. Eventually, Teradata optimizer, takes care of join techniques which best fit.


 


Cheers,


Raja


 

s_1
Enthusiast

Re: Index Cocepts in Teradata

Hi all,


 


can you tell me bellow mentioned which one is better ?


merge join or hash join or nested join  


 


Ratnam