Re: bad performance with a simple select statement

DirkM wrote: >Another idea - have you tried running "update statistics" on this table yet >? > >Gursoy YERLI <gursoy@ata.net> wrote in article ><6ugrue$i7t$1@news.xmission.com>... >> >> Keep in mind that activating PDQ (Parallel Database Query) parameter >forces >> informix to do sequential scan with hash join. Of course before turning >the >> PDQ parameter off (if activated), try other advises. >> >> Gursoy YERLI >> >> lol007@my-dejanews.com wrote: >> >> > Hello: >> > I run INFORMIX-Universal Server Version 9.14.UC1X3 on a SGI/IRIX 6.2 >INDY >> > RS5000SC box. >> > I create a buffered-logging database in a mirrored dbspace. >> > I create the following table : >> > create table test ( >> > f1 int, >> > f2 smallfloat, >> > f3 smallfloat, >> > f4 smallfloat, >> > f5 smallfloat, >> > f6 smallfloat, >> > f7 smallfloat, >> > f8 smallfloat, >> > f9 smallfloat, >> > f10 smallfloat, >> > PRIMARY KEY (f1) >> > );>> > I load the table with a 400K records file. >> > The following simple statement : >> > select * from test where f1=200000;>> > takes 10 seconds !? >> > >> > I even tried the following : >> > begin work; >> > set isolation to dirty read;>> > select {+ USE_INDEX(test,100_1) } * from test where f1=1; >> > commit work; >> > Still 10" long ! Try to set SET EXPLAIN ON and see if the engine is using your USE_INDEX directive. I'm not sure you have written the USE_INDEX directive correctly. Is 100_1 a index name ? Try this (I'm not sure it will work): {+ USE_INDEX(test,f1) } Best Regards, Octav -- Octav Chiriac Phone: (373) 2 21 20 96 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 24 00 83 Moldova, Republic of mailto:com@netinfo-moldova.com