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
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.
Thanks a lot Ravi. Your explanation is simply superb.
If primary index and secondary index is in same amp than how can we tell it as two amp operation....?
Its the same reason as explained in the thread above. Which part you don't understand?
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
1. Teradata which join is better ?
2. Default teradata which join taken ?
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.
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
can you tell me bellow mentioned which one is better ?
merge join or hash join or nested join
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.