Can anyone quote the example in the explain where it clearly shows that it is a NUSI and this is leading to a FULL Table Scan.
NUSI is also an all-amp operation and FTS is also an All amp all rows scan...
Can anyone quote an example of the same as I tried doing it..and it shows as 3) We do an all-AMPs RETRIEVE step from by way of an all-rows scan with a condition of ( " = 'fedrick") into Spool 1 (group_amps), which is built locally on the AMPs....
and when I try dropping the NUSI then also it is giving the same explain. How can I clearly know if it is a FTS or a NUSI from the explain plan? Can anyone please provide the explain steps if it is a NUSI and a FTS...
A FTS is an all amp scan where teradata scans the entire base table. When you create a NUSI teradata creates NUSI subtable and this would be created in each of the amp. Now what this NUSI subtable holds ? it holds SI value, Rowhash of SI value and Base table row id. Most important that ALL the rowid's u have in the nusi subtable is AMP lOCAL.
Now when u fire the query which has nusi column and if the optimiser decides to make use of the NUSI then teradata does the below
It will do an all amp operation on each of the SUBTABLE ( Note this is not on the base table) and if it sees the value it will note the rowid go the same amp where the base table row is located put that in the spool. Each of the amp does the same thing to give the result set.
Conclusion: If optimiser decides to make use of NUSI in the explain u would see the follwing statement " Traverse by index # 4" which means NUSI is being used and when u dont see anything it means its FTS ie scanning the base table rows.
If your doing this on a demo machine then the reason u dont see the index is u have less records in the table and Optimiser( Cost based) is wise enough in TD. It would have thought that cost for doing FTS is effective than the cost if it had to go through the index.
Hi, I have similar request where my base table have around 2400 Million records :(. I have to join it to another table(with around 100 millions of data) based on a NON-INDEXED column. Is it preferred to go for NUSI?? Please suggest.