SEO

#1 (**permalink**) 04-20-2008, 07:40 AM

How many rows in ano_k_out_hem? If only a few, the optimizer will do
the sequential scan because it is more efficient than reading the index
and the data.

Bill

> -----Original Message-----
> From: owner-informix-list@iiug.org [SMTP

wner-informix-list@iiug.org]
> On Behalf Of hajek@nspuh.cz
> Sent: Tuesday, February 15, 2005 5:24 AM
> To: informix-list@iiug.org
> Subject: serial and sequential scan
>
> 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
>

sending to informix-list

#2 (**permalink**) 04-20-2008, 07:41 AM

where is OPTCOMPIND set to???? if 2 then set it to 0

or

export OPTCOMPIND=0

start dbaccess with your problem qry.

Superboer.

"Bill Dare" <dareb@jevic.com> wrote in message news:<1108481901.fe4431bcdc1aee4b5679c325048687fb@ teranews>...
> How many rows in ano_k_out_hem? If only a few, the optimizer will do
> the sequential scan because it is more efficient than reading the index
> and the data.
>
> Bill
>
>
> > -----Original Message-----
> > From: owner-informix-list@iiug.org [SMTP

wner-informix-list@iiug.org]
> > On Behalf Of hajek@nspuh.cz
> > Sent: Tuesday, February 15, 2005 5:24 AM
> > To: informix-list@iiug.org
> > Subject: serial and sequential scan
> >
> > 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
> >
>
> sending to informix-list

#3 (**permalink**) 04-20-2008, 07:44 AM

superboer7@planet.nl (superboer) wrote in message news:<bb790a36.0502160532.2c2ac371@posting.google. com>...
> where is OPTCOMPIND set to???? if 2 then set it to 0
>
> or
>
> export OPTCOMPIND=0
>
> start dbaccess with your problem qry.
>
> Superboer.
>
>
>
> "Bill Dare" <dareb@jevic.com> wrote in message news:<1108481901.fe4431bcdc1aee4b5679c325048687fb@ teranews>...
> > How many rows in ano_k_out_hem? If only a few, the optimizer will do
> > the sequential scan because it is more efficient than reading the index
> > and the data.
> >
> > Bill
> >
> >
> > > -----Original Message-----
> > > From: owner-informix-list@iiug.org [SMTP

wner-informix-list@iiug.org]
> > > On Behalf Of hajek@nspuh.cz
> > > Sent: Tuesday, February 15, 2005 5:24 AM
> > > To: informix-list@iiug.org
> > > Subject: serial and sequential scan
> > >
> > > 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
> > >
> >
> > sending to informix-list

Hi,

As S Kagel wrote, I strongly suggest the same statements !!

You show:
> update ano_k_out_hem set status_apl = "Z" where id_pozad = 2610405

( it means you have already inserted 2610405 rows in this table
.....).

and in other place, you informed there is about 3200 rows in the
table, it means you do insert into <table> and delete from <table>
often.
It means update statistics do not represents the 'reality' of the rows
now in the table ...
Then, you have to run update statistics high as often as possible.
Remember : It can lock that table, if you are using transactions to
change data. I suggest you use "set isolation to dirty read" before
the statement 'update statistics high ...'.

BR,

R Ferronato

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

RE: serial and sequential scan