NUSI Vs. FTS

Database
Enthusiast

NUSI Vs. FTS

Hi,

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

thanku...

5 REPLIES
Enthusiast

Re: NUSI Vs. FTS

Dear TD aspirant,

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.

:)

Cheers
Teradata_DBA
Enthusiast

Re: NUSI Vs. FTS

Hi,

Thank You so much. so you mean in the example above it is always gng for FTS as no. of rows are less and hence, optimiser thinks FTS to be a better option than choosing NUSI...

I never saw the statement "Traverse by index #4" etc.. when I included NUSI in my queries!

Thank you so much DBA :)

Re: NUSI Vs. FTS

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.

Thanks,
Venky.

Re: NUSI Vs. FTS

Hi teradata experts,
I need your help to solve some teradata problems ..........can anybody share his mail id with me..so i can ask questions.......Thanks
Regards
bpshekhawat@gmail.com
Teradata Employee

Re: NUSI Vs. FTS

please post the DDL for more review...

 

Try using Unique SI (composite)... if possible implement PPI for bigger tables...