Path: utzoo!utgpu!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!rutgers!ucsd!sdcsvax!ucsdhub!hp-sdd!hplabs!hp-sde!hpcuhb!hpcilzb!hpcea!hpausla!cjh
From: cjh@hpausla.HP.COM (Clifford Heath)
Newsgroups: comp.databases
Subject: Re: need HELP with index problem
Message-ID: <2820003@hpausla.HP.COM>
Date: 28 Dec 88 03:27:31 GMT
References: <8973@cit-vax.Caltech.Edu>
Organization: HP Australian Software Operation
Lines: 43

Sorry, couldn't get to you by E-mail.  Anyhow, this is interesting to others.

> Hope someone can understand what is going wrong here. I have a table in
> a database that has three indices: 
> a composite unique index made up of date, name1 and slotnumber
> an index of name2
> an index of unitnumber

> there are about 50,000 rows in the table. In about 90% of the rows the
> unitnumber is 0. In time these will be changed to other numbers (the
> slots will be "filled"). 

> HOWEVER, when I update the unitnumber field
> and that field already contains a 0, the update takes forever (about 30
> sec). There is alot of disk accessing.

This is because Informix uses a 16 bit "duplicate number" in order to
maintain the proper (ANSI COBOL required) historical sequencing of
duplicates.  In order to find the BTREE index entry to delete, it cannot
search for the appropriate duplicate by number, since that information
is only in the index, but rather must search the entire chain of
duplicates to find the index entry that matches the record number being
deleted.  In this case up to 45000 entries, or on average over 22000
entries must be searched.  Because of the time-ordering, recent entries
are at the end of the list, resulting in worst-case searching.

This is a good case for requesting Informix to add a new flag to be used
for creation of duplicate indices, saying "Don't bother time-ordering
duplicates".

BTW, if you get to the limit of 65K duplicates in a chain, bcheck will
*ALWAYS* report "index out of order", simply because the duplicate number
wraps around and bcheck notices it.  I don't know what the wrapping does
to time-ordering of duplicates, but it doesn't seem to corrupt the index
otherwise.

Disclaimer:
This information is based on version 2.10 of C/ISAM* source code, and so
may be out of date.  * C/ISAM is a trademark of Informix Corp.
This article contains only opinions that are my own, and may be wrong.

Clifford Heath, HP Australian Software Operation. ACSnet: cjh@hpausla.oz,
Internet: cjh%hpausla@hplabs.HP.COM, UUCP: hplabs!hpfcla!hpausla!cjh