serial and sequential scan
Michal Hajek asked why IDS 7.31 on HP-UX 10.20 used a sequential scan for an update on a serial primary-key column, though statistics were updated regularly. Ben Thompson said a small table or stale statistics could make the optimiser choose a scan, and a cost of 3 was low. The table holds 0-5000 rows. Scottishpoet asked for dbschema -hd output and questioned the update statistics syntax. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
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.
It is table used for communication, so it holds betwen 0-5000 rows. Just now there is about 3200 rows.
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.
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?