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?
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?