RE: no index used
With a cost of 2 I would say the statistics are old
run update statistics on this table then try again.
update statistics high on the columns with indexes might be good too
Will
>===== Original Message From steinhoefel@dcs-systeme.de =====
>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
------------------------------------------------------------
This e-mail has been sent to you courtesy of OperaMail, as a free service from
Opera Software, makers of the award-winning Web Browser, Opera. Visit us at
http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail
account is waiting at: http://www.operamail.com/
------------------------------------------------------------