Difference between NUSI and Hash ordered NUSI?

Database
Enthusiast

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

2 REPLIES
Enthusiast

Re: Difference between NUSI and Hash ordered NUSI?

Hi Experts,

Any suggestions on the above pls?

Cheers!

Nishant

Enthusiast

Re: Difference between NUSI and Hash ordered NUSI?

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