Oninit cdi / Archive
Talk to an expert →

serial and sequential scan

Posted in 2005

Topics: Performance & Tuning, Server Administration, Platform-Specific Issues, Clustering, Grid & MACH11, Versions, Editions & End-of-Life

hajek@nspuh.cz — — source: Usenet: comp.databases.informix

Hi, is this normal behaviour (IDS 7.31, hp-ux 10.20)? from dbschema: create table ano_k_out_hem ( id_pozad serial not null , num_vysl decimal(14,6), text_vysl char(30), ....... primary key (id_pozad) constraint u3332_3326 ); from sqexplain.log: QUERY: ------ update ano_k_out_hem set status_apl = "Z" where id_pozad = 2610405 Estimated Cost: 3 Estimated # of Rows Returned: 1 1) ano_k_out_hem: SEQUENTIAL SCAN Filters: amis.ano_k_out_hem.id_pozad = 2610405 from dbaccess table info: Index name Owner Type Cluster Columns 1006_3439 amis unique No id_pozad So - why the sequential scan? (Update statistics (medium) is being executed regularly) Thanks, Michal

↪ replying to hajek@nspuh.cz
Ben Thompson — — source: Usenet: comp.databases.informix

hajek@nspuh.cz wrote: > So - why the sequential scan? (Update statistics (medium) is being > executed regularly) How many rows are there in this table? If it's not very many it may be quicker to do a sequential scan than refer to an index. Ben.

↪ replying to Ben Thompson
hajek@nspuh.cz — — source: Usenet: comp.databases.informix

It is table used for communication, so it holds betwen 0-5000 rows. Just now there is about 3200 rows.

↪ replying to hajek@nspuh.cz
Ben Thompson — — source: Usenet: comp.databases.informix

hajek@nspuh.cz wrote: > It is table used for communication, so it holds betwen 0-5000 rows. > Just now there is about 3200 rows. I think the optimiser goes on how many rows there were when update statistics was last ran. If this table changes frequently then your statistics get out of date very quickly. Looking at your query plan an estimated cost of 3 is not very high. Ben.

↪ replying to Ben Thompson
scottishpoet — — source: Usenet: comp.databases.informix

whats the output of dbshema -hd <tabname> -d <databasename>? update statistics (medium) gives me a syntax error . Looks like the ( )around the medium is not valid. so maybe this is not having the desired affect? update statistics medium moans that only the dba can do this so thatdoesn;t work on all my tables either what happens if you run update statistics medium for table ano_k_out_hem and re run the dbschema above? does the out put chnage?

Glossary