RE: no index used

Be sure that you "Update Statistics" in the manner documented in Informix release notes. Otherwise, you may not obtain the desired results. Step 3 in the following excerpt from PERFDOC_7.3, pertains to multi-column indexes: For each table that your query accesses, build data distributions according to the following guidelines: 1. Run UPDATE STATISTICS MEDIUM for all columns in a table that do not head an index. This step is a single UPDATE STATISTICS statement. The default parameters are sufficient unless the table is very large, in which case you should use a resolution of 1.0, 0.99. For example, suppose you have a table t1 with columns a, b, c, d, e, and f with the following indexes: ix_1 defined on columns a, b, c, d ix_2 defined on columns a, b, e, f ix_3 defined on column f Run the following UPDATE STATISTICS statement to obtain data distributions on the columns in table t1 that do not head an index: UPDATE STATISTICS MEDIUM FOR TABLE t1(b,c,d,e) DISTRIBUTIONS ONLY; With the DISTRIBUTIONS ONLY option, you can execute UPDATE STATISTICS MEDIUM at the table level or for the entire system because the overhead of the extra columns is not large. 2. Run UPDATE STATISTICS HIGH for all columns that head an index. For the fastest execution time of the UPDATE STATISTICS statement, you must execute one UPDATE STATISTICS HIGH statement for each column. In addition, when you have indexes that begin with the same subset of columns, run UPDATE STATISTICS HIGH for the first column in each index that differs. For example, suppose you have the following indexes on table t1: ix_1 defined on columns a, b, c, d ix_2 defined on columns a, b, e, f ix_3 defined on column f Run UPDATE STATISTICS HIGH on column a by itself. Then run UPDATE STATISTICS HIGH on columns c and e. UPDATE STATISTICS HIGH FOR TABLE t1(a); UPDATE STATISTICS HIGH FOR TABLE t1(c); UPDATE STATISTICS HIGH FOR TABLE t1(e); In addition, you can run UPDATE STATISTICS HIGH on column b, but this step is usually not necessary. 3. For each multicolumn index, execute UPDATE STATISTICS LOW for all of its columns. For the single-column indexes in the preceding step, UPDATE STATISTICS LOW is implicitly executed when you execute UPDATE STATISTICS HIGH. For the sample indexes in the preceding step, run the following UPDATE STATISTICS statement to update sysindexes and syscolumns: UPDATE STATISTICS FOR TABLE t1(a,b,c,d); UPDATE STATISTICS FOR TABLE t1(a,b,e,f); 4. For small tables, run UPDATE STATISTICS HIGH. There are numerous utilities and scripts available, which facilitate this tedious process. -----Original Message----- From: steinhoefel@dcs-systeme.de [mailto:steinhoefel@dcs-systeme.de] Sent: Sunday, February 27, 2000 12:29 PM To: informix-list@iiug.org Subject: Re: no index used Thanks again for your answers, I had already tried to change OPTCOMPIND from 2 to 0 - with no effect - and used "update statistics" before I posted my question. Today I tried a "update statistics high", and now I get better answers from the server, but it is by far not what I expect from it. It now uses simple indexes in the matter I'd expect - most of the time - but it still refuses using combined indices. There is a combined index on (kmi,mti) as you can see from my post of the table definition. On that table I do selects with a lot of conditions, for instance 10 kmi conditions each one with 40 mti conditons in the following matter: select pkey from table1 wherekmi = 5 and (mti between 10 and 39 or mti between 60 and 99 or mti or mti between...) or kmi = 5262 and (mti between 0 and 4 or mti between 50 and 54 or mti between...). For such selects the system does a table scan. This is not necessary, the answer is just a small part of the whole table. That these my theory is rigth I see at the results of selects just with kmi (let out all mti conditions): it's very fast now, much faster than a table scan. Even if I try just two kmi each one with one mti condition the Index is not used. Today I encountered another problem: kmi and mti are spatial index columns for lkr and lkh(x and y). So against all theory I tried to use lkr and lkh directly, with no spatial index. So I did a index on lkr, and selects with very small answers where accelerated fine, but if had big answers (a lot of rows) it was a catastrophe. Selecting the whole table with the index was as 15 times !!! as slow as a table scan. Now my question: Is it generally not adviceable to use indices on float columns in informix? If I cannot resolve my mti problem I plan to put kmi and mti together into a double (I need at least 32 bit for kmi and 7 bit for mti). So would this be a useful try?