All Forums Database
17 Dec 2013
Difference between NUSI and Hash ordered NUSI?

Hi all,
Need a suggestion on defining  a SI on a column which has very low cardinality (0 or 1 always ). this column would be used
in most of the cognos reports.
We are thinking of making it as NUSI .Can you pls suggest whether its fine to define it as a standard NUSI or would need
to make it as hash ordered NUSI... ?

17 Dec 2013

Hi Experts,
Any suggestions on the above pls?

Raja_KT 1246 posts Joined 07/09
17 Dec 2013

Hi Nishant,


I have never encountered that situation in real time. However,my findings are highlighted below:


USIs are always stored in hash order on an AMP, while NUSIs can be stored either in hash order or in value order on an AMP.


hash-ordered NUSI goes well with = on the secondary index column.VOSIs for processing range conditions (like between ...) and  conditions with either an equality or inequality on the secondary index column


If a value-ordered index is defined on the column that


satisfies a range predicate from this list, then a full-table scan is not necessary to satisfy the


condition and the Optimizer merely scans a subset of the value-ordered index subtable instead.


I think you want an index to be used instead of full table scan.


So my suggestion is to test your cases  and run the explain.





Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.