All Forums General
jainayush007 50 posts Joined 03/11
30 Jul 2011
Difference between Single Table Join Index, NUSI and Hash Index


Really wonder how these indexes are different, which one to use and where to use. Any help would be greatly appreciated.


kri 3 posts Joined 08/11
02 Aug 2011

harsh index is used for read the data form the parsing engine and addressing where to keep how to keep in amp level where as it collects row level information
where as nusi it will take duplicate rows when u using uyilities that time if primary index is there it will show u in err
it gets problem when u are working at staging level index get problem

jainayush007 50 posts Joined 03/11
12 Aug 2011

Sorry, Didnt understand that...

Would it be possible to show with an example?

barryhull 31 posts Joined 05/11
16 Aug 2011

A hash index is just a special form of a single table join index, so you can create a single table join index to do the same thing as a hash index.

With a NUSI, the DBMS generates a subtable on each AMP containing the values for that NUSI into rows in th subtable on that AMP. Data row pointers (back into the original table) are stored on each NUSI row, so that for any given NUSI value, the DBMS can locate the data rows for that value. Because the same NUSI values can exist on multiple AMPS, when you access the data table using the NUSI, it is an "all-AMP" operation. Each AMP has to access the NUSI to find the data rows for the particular NUSI value or values. The NUSI rows are stored in hashing order on each AMP so that if the NUSI value is known, it can be quickly accessed. NUSI's can also be stored in "value" order which is useful for NUSI ranges.

With a single table join index, you control the distribution of the index by specifying a primary index. This, in effect, would allow you to create an alternate primary index for a table. This is useful if you want to access a table on something other than the primary index and make it a single-AMP operation as opposed to an all-AMP operation, which is much more resource intensive. It is also useful if you plan on joining the base table to other tables on different columns. You can also put conditions (a WHERE clause) on the index so that only rows meeting the criteria are indexed. Join indexes may contain several columns from the original table, or can contain a ROWID that points back to the original data row, or both. Finally, single table join indexes can also be used to aggregate data in the index, so that queries that use the aggregation do not have to access the table and can instead get the result from the join index.

Alison 2 posts Joined 04/09
22 Aug 2011

Just a minor correction. Hash Indexes are not a form of Join Index. They are similar in usage to Single Table Join Indexes, but aggregation, secondary indexing, and triggers are not permitted on Hash Indexes.
Hash Index automatically include the base table Primary Index value, no other index does that.


barryhull 31 posts Joined 05/11
22 Aug 2011

Hi Alison, your points are all well taken. What I meant by my comment was that you could create a single table join index to do what a hash index does, plus you can do a lot more with join indexes. So, I don't really see a reason to create a hash index, other than it might be simpler from a syntax standpoint.

Is there something that you can do with a hash index that you can't do with a single table join index?

18 Apr 2014

Nice Info.. Thanks Barry & Alison!

Santanu84 122 posts Joined 04/13
21 Apr 2014

yup ... good discussion on HI and STJI ... all consolidated ... :-)
Thanking You

You must sign in to leave a comment.