Hello all. I read an interesting article on an IBM website about
evaluating the uniqueness of indexed columns. One part that was not
clear to me was how a composite indexes columns should be ordered on
creation and how that effects the uniqueness measurement. For example,
I have a table called tblRecords that has 93416076 rows.

Amongst other indexexs, I have one index in particular thats got me
questioning its formation based on IBMs instructions on how to measure
uniqueness. The index looks like this:

create index "informix".cl_tapekey_v2 on "informix".tblRecords
(subclient_key,claim_no,claim_line_key) using btree ;

Now in order to measure an indexes uniqueness, I use the following
SQL:
select tabname, idxname, nrows, nunique
from systables t, sysindexes i
where t.tabid = i.tabid
and t.tabid >99
and nrows >0
and nunique >0
and tabname = 'tblRecords'
and idxname = 'tblRecords_pk_v2'

And my results look like this:
tabname tblRecords
idxname tblRecords_pk_v2
nrows 92177086
nunique 90

And again, per the IBM aritcle to get the percentage of uniqueness I
use <percentage of uniqueness> = ((nunique/nrows)*100) which yields a
pretty low percentage, something like 9.7nnnnnnnnnnn!

I think this is because the number of unique values in my "head
column" (first column in index creation statement) of the composite
index is a lowly 90. But, I am not sure I am interpretting this
correctly or if the "head column" is actually the most important part
of the composite index? If the head column is the heavy hitter, should
the this always be a column with the most unique data?

On Wed, 28 Apr 2004 11:25:13 -0400, sumGirl wrote:

Yes, reordering the keys so that the first key column has a higher filter
value (ie selects fewer rows) than subclient_key will improve the NUNIQUE
value. However, if you are using data distributions (UPDATE STATISTICS
MEDIUM/HIGH ... ) this stat is rarely used. For best results maintain at
least the level of stats recommended in the Performance Guide. BTW, this does
not mean that reordering the keys will not improve performance. If you do not
need this particular key ordering to prevent common sorts to fulfill ORDER BY
clauses, by all means reorder the keys. The locality of nodes and leaves in
the index will improve reducing physical IOs during index scans.

Art S. Kagel

> Hello all. I read an interesting article on an IBM website about evaluating
> the uniqueness of indexed columns. One part that was not clear to me was how
> a composite indexes columns should be ordered on creation and how that
> effects the uniqueness measurement. For example, I have a table called
> tblRecords that has 93416076 rows.
>
> Amongst other indexexs, I have one index in particular thats got me
> questioning its formation based on IBMs instructions on how to measure
> uniqueness. The index looks like this:
>
> create index "informix".cl_tapekey_v2 on "informix".tblRecords
> (subclient_key,claim_no,claim_line_key) using btree ;
>
> Now in order to measure an indexes uniqueness, I use the following SQL:
> select tabname, idxname, nrows, nunique from systables t, sysindexes i where
> t.tabid = i.tabid
> and t.tabid >99
> and nrows >0
> and nunique >0
> and tabname = 'tblRecords'
> and idxname = 'tblRecords_pk_v2'
>
> And my results look like this:
> tabname tblRecords
> idxname tblRecords_pk_v2
> nrows 92177086
> nunique 90
>
> And again, per the IBM aritcle to get the percentage of uniqueness I use
> <percentage of uniqueness> = ((nunique/nrows)*100) which yields a pretty low
> percentage, something like 9.7nnnnnnnnnnn!
>
> I think this is because the number of unique values in my "head column"
> (first column in index creation statement) of the composite index is a lowly
> 90. But, I am not sure I am interpretting this correctly or if the "head
> column" is actually the most important part of the composite index? If the
> head column is the heavy hitter, should the this always be a column with the
> most unique data?