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?
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.
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.
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.
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.
Parthasarathi Dutta Sharma
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.
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.
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.
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.