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... ?
 
Cheers!
Nishant
 
 

17 Dec 2013

Hi Experts,
Any suggestions on the above pls?
Cheers!
Nishant

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.

 

Cheers,

 

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.