RE: Primary key, Foreign key and indexes in Informix

Andrew Hamm wrote > There is, however, a potential problem with foreign key indexes, and it > comes about due to an effect that causes trouble with any indexes that have > a very poor data distribution. To understand the problem you need to know a > bit about the internal structure of an index. What would you say about a table that returns the following - select count(*), id_code from tab1 group by id_code 1254696 X 1069 Y 1530855 Z Tab1 is only ever added to, but is used a lot in queries. id_code is a non composite index. id_code is a foreign key on another table. Field and table name changed to protect the innocent ( or guilty!! ) Colin Bull c.bull@videonetworks.com sending to informix-list

"Colin Bull" <c.bull@videonetworks.com> wrote in message news:c6nsl3$dr6$1@terabinaries.xmission.com... > What would you say about a table that returns the following - > > select count(*), id_code from tab1 group by id_code> > 1254696 X > 1069 Y > 1530855 Z > > Tab1 is only ever added to, but is used a lot in queries. > id_code is a non composite index. > id_code is a foreign key on another table. > As a serial. create composite index on (id_code,serial), remove index on id_code. > Field and table name changed to protect the innocent ( or guilty!! ) > > > Colin Bull > c.bull@videonetworks.com > > sending to informix-list

Colin Bull wrote: > > What would you say about a table that returns the following - > > select count(*), id_code from tab1 group by id_code> > 1254696 X > 1069 Y > 1530855 Z > > Tab1 is only ever added to, but is used a lot in queries. > id_code is a non composite index. > id_code is a foreign key on another table. Ouch! I'd say, fragment - that will satisfy equality clauses asking for X Y or Z, and you can eliminate that index. I would actually expect that add operations are fairly cheap; hoping that the database keeps a pointer to the end page, or maybe a freelist of slots in the linked list of rowids. But pity the poor person who has to change a value or rollback a transaction. If it's definitely a 3-valued index then fragment by expression for = "X", = "Y" and ="Z". "Y" would probably be better as an OTHERWISE (or is it ELSE?) clause since it will collect other stray values that creep in. If you don't want other stray values to creep in, then don't use the OTHERWISE clause, and the engine will spit it out. If the values of X Y and Z can change occasionally or be added to, then you need to consider small administrative activity on the fragments when new values arrive. This is not uncommon for slowly creeping fragmentation rules. Since it's FK'd to another table, can you make the entry programs validate it? However, if you do use an explicit list of values in the fragmentation expressions, then you'll still get an engine error not unlike a FK violation if a naughty value is inserted.