All Forums Database
anill 26 posts Joined 08/07
26 Sep 2007
diff between USI and NUSI

Can you plz give answer for this how can unique secondary index and non unique secondary index are differ?which can give more efficiency in query accessing?and which one is more preferable?

leo.issac 184 posts Joined 07/06
26 Sep 2007

Secondary Indexes could be used as alternate access paths for table rows.In certain situations when the Primary Index(NUPI) could not contribute for uniqueness of rows , Secondary Indexes are implemented to impose the uniqueness for these rows.There are two types of secondary Indexes, USI and NUSI. As the name implies USI does not accommodate duplicate index values while NUSI does.The purpose of using each one of them depends upon the requirement of user.Secondary Indexes are maintained as sub-tables(containing index columns and Row ID of base table row).NUSI subtable rows are physically located on Amp-Local basis i.e., the sub-table rows corresponding to a particular base table row reside in the same AMP as that of the base table row.While the sub-table row of USI could be located on a different AMP.Usage of NUSI is subject to selectivity of the index columns.

anill 26 posts Joined 08/07
27 Sep 2007

thank u Leo Issac.But i have one doubt plz rectify it...your answer is USI's sub table stores on different AMP and NUSI sub table storeson same AMP. But I saw USI is 2-AMP operation and NUSI is all-AMP operation.but I don't know internal functionality of SI.

leo.issac 184 posts Joined 07/06
27 Sep 2007

Anil,True! USI access is 2- AMP operation while NUSI acces is all AMP operation.You can find detail explanation in documentation.I would try to explain it in short hereSecondary Indexes are maintained in a sub-table.Each sub-table row would store te index values and row-id of the base table row.To retrieve a row from the base table based on qualifying condition on SI columns .teradata would first read the sub-table row corresponding to the SI column condition.Once the Sub-Table row is located ,teradata will pick up the row id from this row and reads the base table to fetch the answer set. Since sub-table and base table rows are located on different amps a USI access would be a 2-AMP access.In case of NUSI which are stored in AMP local basis.Teradata would first do an ALL AMP search based on condition on NUSI columns in the query.Once the NUSI values are found on a particular AMP, teradata would do a search of base table rows in that particular AMP.

forpartha 4 posts Joined 06/10
22 Dec 2010

This post is over 3 years old, but for people still asking this question, one big difference between USI and NUSI is in the ordering.

USI is hash ordered; the same hash function used in the primary index is used.

NUSI, on the other hand, is value ordered, so each AMP maintains its own sorted list of values.

Regards,

Parthasarathi Dutta Sharma

forpartha 4 posts Joined 06/10
25 Dec 2010

a correction there, a NUSI *can be* value ordered as well. I haven't figured out the utility of having hash ordered NUSI but that doesn't mean its not allowed.

Regards,

Parthasarathi Dutta Sharma

Arafat 2 posts Joined 07/11
23 Apr 2012

Hi,

I have a question. Why can't a NUSI be hash distributed to the subtables in different amps just as it is done in case of a USI and in that case the non unique NUSI values can be placed in the same AMP's sub table where it is hashed to with mutiple base table Row IDs for that NUSI value . In this case the retrieval process should be less than an all Amp operation.

dnoeth 4628 posts Joined 11/04
24 Apr 2012

You might have a column with a skewed distribution, e.g. the same default value or NULL in 20% of the rows, which would result in a heavily skewed subtable.

And NUSI maintenance would be much higher because of the neccessary inter-AMP communication.

Dieter

Dieter

ToddAWalter 316 posts Joined 10/11
24 Apr 2012

It is possible to create an index that is hash distributed as you describe. Use a single table join index with a primary index of the fields you want it hash distributed on. You can have ROWIDs in the index so it can work like a NUSI or you can cover the queries that you will be running with this access path.

Arafat 2 posts Joined 07/11
25 Apr 2012

Thanks guys..

s@ir@m 35 posts Joined 05/13
17 Oct 2013

Hi all,
 
why Mload suports nusi.
fastload does't suports ?
what is the resion can you please elabrate .
Ratnam

dnoeth 4628 posts Joined 11/04
17 Oct 2013

Hi Ratnam,
FastLoad is for loading empty tables, so there's no need to support secondary indexes as you can simply created them after load.
 
Dieter
 

Dieter

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

Hi Dieter,
 
can u e explain why NUSI takes all AMP Operation,
give me Best example 
 
Thanks
Ratnam

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

Hi Ratnam,
I know I am not Dieter but let me try to answer your question:
In a NUSI access the message from PE is brodcasted to all the amps, the reason is to scan all the NUSI subtables, once the NUSI subtable is scanned the selected ROWID's are copied to spool and then only the amps containing rows with these ROW IDs  are activated. So in start the NUSI access is all amp but in second step it can be all amp or some selected amps based on the row ids spooled from Subtable.
To understand completely read the manual for detailed step by step understanding.
 

Khurram

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

Thqu 

You must sign in to leave a comment.