All Forums Database
24 Jan 2010
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 AdvanceGanesh.J

ravi.videla 21 posts Joined 09/09
27 Jan 2010

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 recordWhere 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.

01 Feb 2010

Thanks a lot Ravi. Your explanation is simply superb.

vinay7 1 post Joined 02/13
14 Feb 2013

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

Qaisar Kiani 337 posts Joined 11/05
16 Feb 2013

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

22 Feb 2013

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@ir@m 35 posts Joined 05/13
18 Dec 2013

Hi all,
1.  Teradata which join is better ?
2. Default teradata which join taken ?

M.Saeed Khurram 544 posts Joined 09/12
18 Dec 2013

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.


Raja_KT 1246 posts Joined 07/09
18 Dec 2013

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.





Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

s@ir@m 35 posts Joined 05/13
19 Dec 2013

Hi all,
can you tell me bellow mentioned which one is better ?
merge join or hash join or nested join  

M.Saeed Khurram 544 posts Joined 09/12
19 Dec 2013

Hi Ratnam,
Basically these three are join processing strategies used by Teradata, and decided by the oprimizer based on number of factors. I think they are not availbel for the developer to choose between. You can see these words in the explain plan and on the basis of explain you can tune your queries to use one of these. 
Any of these types can be better based on the workload and query types. Merge Join is mostly used when both the tables participating in join have PI on joining columns. Nested joins are mostly useful in OLTP applications where one table is higly selective and the other table is processed based on a selected row from one table. 


You must sign in to leave a comment.