I have a table with following columns:
division group email

Then, i create a non-unique index for:
division group

I insert the following data:
division group email
1 A a...@hotmail.com
1 A b...@hotmail.com

With this set of data in table, will the index table consist of 1
index record, i.e.
division group
1 A
or will it contains 2 index records, i.e.
division group
1 A
1 A

The DBMS is Informix 7.32.FC2.

Thanks for prompt reply.

falconsoon@gmail.com wrote:
> I have a table with following columns:
> division group email
>
> Then, i create a non-unique index for:
> division group
>
> I insert the following data:
> division group email
> 1 A a...@hotmail.com
> 1 A b...@hotmail.com
>
> With this set of data in table, will the index table consist of 1
> index record, i.e.
> division group
> 1 A
> or will it contains 2 index records, i.e.
> division group
> 1 A
> 1 A
>
> The DBMS is Informix 7.32.FC2.
>
> Thanks for prompt reply.
>

There is typically only one key entry for each unique value in a
duplicate key index each followed by a list of rowids containing that
value. I say typically because if there are more matching rowids for a
key value than can fit on a single index leaf node page the following
leaf node or nodes will also contain the same key value.

An aside: This latter implementation detail is the reason that highly
duplicated keys are inefficient. The engine has to follow the leaf node
next pointers from page to page to retrieve all of the matching rowids
for the given key value. It is not significantly less efficient to
process all of the matching partial keys in the same index if an
additional column which makes the total key more unique is appended to
the highly duplicated key and much more efficient to process any subset
of rows if a value is supplied that allows the engine to use the entire
expanded key.

Art S. Kagel
Oninit


================================================== =========================================
Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.oninit.com/home/disclaimer.php

================================================== =========================================