Re: Index for Informix Dynamic Server

William Lai wrote: > > Hello, > > We alreay upgrade our database server from SE5.X to IDS 7.3. After we > convert all the old data to the new server, we have some problems on the > index search. For example, a table: > > table : ith ( ithitno, ithname ), total 1,500,000 records > index : ith_1 on ithitno, ithname > > when I execute the following SQL statement : > > SELECT * FROM ith WHERE ithitno="ABC" ( "ABC" is not a record in ith )> > it take a very long time ( few minutes ) for the SQL to return "no record > found". At the end we find that the SQL statement only use a sequential > search to find the record "ABC". > > How can I control the use of the index on IDS. Please help me. Thanks In 7.3 you can control the indexes many ways actually. You can use the Oracle-like hints to force the use of a particular index or to suppress the use of a particular index. You can change the optimizer's goal from the default of finding the fastest way to return all rows to the fastest way to return the first row found. HOWEVER, this is not your problem. SE 5.xx kept only limited statistics and was rather dumb about how it chose which index to use. IDS 7.30's optimizer is VERY smart and rarely makes mistakes, but, it needs lots of accurate statistics. In the release notes file: SERVERS_7.2 there are a set of recommendations about how best to maintain these stats. In the IIUG Software Repository there are several packages that include programs and/or scripts to do this easily. Included in that list is my own dostats.ec (if you have ESQL/C, actually it should even compile with 4GL 7.xx) which is found in the package utils2_ak. This is in my, not so humble, opinion the best of the lot, and since it is free I can promote it shamelessly without guilt. I just uploaded a new version of that package about 1 1/2 weeks ago but it has not made it online yet (hint hint Mike). The version that is there is just fine - the new one generates scripts that MAY run a little faster if you have indexes on a single column. Get and run dostats or another of the update stats programs or do the update statistics MEDIUM/HIGH/LOW manually, just get it done, and yourproblem with not using this index will surely go away. Art S. Kagel