no index used

Hi, our informix 7.22 database server refuses using indexes at all. Even if I do a very simple query like "select kee from table1 where kee = 5" where kee is an integer column and has an index with just that column the index is not used. Even the primary index is not used. The table has 250000 rows. The query plan is always "sequential scan". How can I "switch on" the indices, or how can I provide a query plan manually? The indeces are there (to be seen in the database explorer and the creation of another index is forbidden by the system with the message "index already defined for that column" (or so)), and I have already updated the statistics. We have the same database (created by the same sql script) in ms sql server, there's everything fine. Thank you in advance Jens Steinhoefel

steinhoefel@dcs-systeme.de wrote: > > Hi, > our informix 7.22 database server refuses using indexes at all. Even if > I do a very simple query like "select kee from table1 where kee = 5" > where kee is an integer column and has an index with just that column > the index is not used. Even the primary index is not used. The table has > 250000 rows. The query plan is always "sequential scan". How can I > "switch on" the indices, or how can I provide a query plan manually? > The indeces are there (to be seen in the database explorer and the > creation of another index is forbidden by the system with the message > "index already defined for that column" (or so)), and I have already > updated the statistics. > We have the same database (created by the same sql script) in ms sql > server, there's everything fine. > > Thank you in advance > Jens Steinhoefel Could you please post the table schema and the SET EXPLAIN ON output? -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

Thank you for your answers, here is the creation script CREATE TABLE table1 ( kee CHAR(20), flags INTEGER, crtAt DATETIME crtBy INTEGER, modAt DATETIME modBy INTEGER, modCnt INTEGER, projKey INTEGER, pkey CHAR(20), ckey CHAR(20), xrg SMALLINT, xst CHAR(3), xjm INTEGER, xga CHAR(1), xgw INTEGER, xzk SMALLINT, xbj SMALLINT, xah CHAR(9), lkr FLOAT, lkh FLOAT, kmi INTEGER, mti SMALLINT, cmi SMALLINT, kti FLOAT, PRIMARY KEY (kee), FOREIGN KEY (pkey) REFERENCES table2 (kee)); CREATE INDEX projKeyIndex on table1 ( projKey ASC); CREATE INDEX kmiIndex on table1 ( kmi ASC); CREATE INDEX mtiIndex on table1 ( kmi ASC, mti); CREATE INDEX mmmIndex on table1 (mti); CREATE INDEX cmiIndex on table1 ( kmi ASC, mti, cmi); CREATE INDEX ktiIndex on table1 ( kti ASC); I tried the following selects: select kmi from table1 where kmi = 5 -> table scan select mti from table1 where mti = 5 -> table scan select * from table1 where kmi=5 and mti=5 ->index mtiIndex used The result set of the first and third select is 0 rows, of the middle one 100 rows. There are 12 distinct values in the column kmi (that means 20.000 rows have the same kmi) and 2500 distinct values in mti (100 rows per mti) In my opinion the answer to the first and third statement should be there in no time, just look into the index, nothing there with that value - zero rows answer. Here is the optimizer output for the first statement ABFRAGE: ------ select kmi from table1 where kmi = 5 Voraussichtliche Kosten: 2 "costs" Voraussichtliche # von ausgegebenen Zeilen: 5 "rows" 1) informix.pktlage: SEQUENTIELLER SCAN Filter:informix.table1.kmi = 5 I'm still interested in how to specify a query plan manually in informix. Thanks for any advice Jens Steinhoefel

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?

steinhoefel@dcs-systeme.de wrote: > Thank you for your answers, > here is the creation script > > CREATE TABLE table1 ( > ... > FOREIGN KEY (pkey) REFERENCES table2 (kee));> > CREATE INDEX projKeyIndex on table1 ( projKey ASC); > CREATE INDEX kmiIndex on table1 ( kmi ASC); > CREATE INDEX mtiIndex on table1 ( kmi ASC, mti); > CREATE INDEX mmmIndex on table1 (mti); > CREATE INDEX cmiIndex on table1 ( kmi ASC, mti, cmi); > CREATE INDEX ktiIndex on table1 ( kti ASC); You don't need all these indexes. Specifically, get rid of kmiIndex & mtiIndex. cmiIndex would support all accesses supported by them. You should be left with the following. CREATE INDEX projKeyIndex on table1 ( projKey ASC); CREATE INDEX mmmIndex on table1 (mti); CREATE INDEX cmiIndex on table1 ( kmi ASC, mti, cmi); CREATE INDEX ktiIndex on table1 ( kti ASC); Rudy

steinhoefel@dcs-systeme.de wrote: > ... > 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 where> kmi = 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. I thought you said that you have 12 distinct values of kmi. If so, then the fastest way to execute a query that needs to return rows of 10 kmis is the sequential scan. > 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. This may be related to the couple of redundant indexes you had - the Optimizer could be getting confused. Indexes whose columns are identical in order to the lead columns of a bigger index are redundant. (If the smaller index is a unique one, then the bigger index is redundant). Try the query again after dropping "redundant" indexes. > 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. When a table has many indexes, it is advisable to UPDATE STATISTICS for the table whenever you add a new one. > > Now my question: Is it generally not adviceable to use indices on float > columns in informix? Not that I know of. > > 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? Rudy

Have you tried an order by xxx ? where xxx is the indexed column.