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 that
doesn;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?

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
>

Art,

I think you mean "UPDATE STATISTICS MEDIUM FOR TABLE tabname
DISTRIBUTIONS ONLY;

I ran a test on 9.40.FC5 for the stores_demo database and found the
MEDIUM for both table and database level did update the systables,
sysindexes and syscolums. It does only get a sample of the data but
you do not have to follow up with a low or high to update the system
tables.

Have you seen different behavior? If so what version?

Thanks

Roy

Roy Mercer wrote:
> Art,

> I think you mean "UPDATE STATISTICS MEDIUM FOR TABLE tabname
> DISTRIBUTIONS ONLY;

Yeah, don't know what I was smoking that night. 8-(

Art S. Kagel

> I ran a test on 9.40.FC5 for the stores_demo database and found the
> MEDIUM for both table and database level did update the systables,
> sysindexes and syscolums. It does only get a sample of the data but
> you do not have to follow up with a low or high to update the system
> tables.
>
> Have you seen different behavior? If so what version?
>
> Thanks
>
> Roy
>

Thanks to all for your answers.
Art: I have dostats compiled (I tried to use myexport, but after
compiling, it coredumps on first index :-() I will try dostats, it
works with no problem.
Does later "update statistics" clear "update statistiscs high"
optimizing hints?
Michal

hajek@nspuh.cz wrote:
> Thanks to all for your answers.
> Art: I have dostats compiled (I tried to use myexport, but after
> compiling, it coredumps on first index :-() I will try dostats, it

Odd, what's core dumping, myschema or sqlunload? Myschema, I'd guess. Can
you send me the exact myexport commandline, a stack trace from the core dump
and a copy of the dbschema -ss output? I'd like to track down the problem
and fix it.

> works with no problem.
> Does later "update statistics" clear "update statistiscs high"
> optimizing hints?
> Michal
>
Only if the 'DROP DISTRIBUTIONS' clause were to be included.

Art S. Kagel