Re: serial and sequential scan
hajek@nspuh.cz wrote:
UPDATE STATISTICS MEDIUM FOR TABLE...
Is insufficient. Unlike HIGH and LOW, MEDIUM does NOT update the systables,
syscolumns, and sysindexes records. It is the information in the latter
that IDS uses to determine the cost of using a particular index. I would
suggest that you run AT LEAST the following and try again:
UPDATE STATISTICS MEDIUM FOR TABLE ano_k_out_hem;
UPDATE STATISTICS HIGH FOR TABLE ano_k_hem( id_pozad );
This is the MINIMUM set of stats that you need for a single table with a
single index. I assume you database is more complex than this one table and
that most tables, perhaps including this one, have more than the single
index on the primary key. So, I strongly suggest that you get my dostats
utility or one of the two scripts that implement the recommended UPDATE
STATISTICS protocols detailed in the Performance Guide. Dostats is part of
the package utils2_ak which, along with the scripts referred to, resides in
the IIUG Software Repository.
Art S. Kagel
> 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
>