Re: can someone please help me interpret sqexplain.out results?
Hi all,
As everybody can see in all tables the 'path' is "index"....
I guess the "Estimated Cost" is very high.
Looking for this 'window', I can say:
Why not run
update statistics for table <tabname>;
update statistics high for table <tablename> (index columns); ??????Try it and be happy !!!!!!!!!
BR. R.Ferronato
dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0405081414.4f4e7e67@posting.google.com>...
> oops, one small typo, this would be better :
>
> select address.address_key
> from address, member
> where address.address_key = member.address_key
> and member.subclient_key = 26>
> dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0405080028.3d85f6db@posting.google.com>...
> > hmmm,
> >
> > looking at the SQL, I cn't help thing the 2 subqueries
> >
> > (select address_key
> > > from address
> > > where address_key in(select address_key
> > > from member where
> > > subclient_key = 26)
> > > )
> >
> > Could maybe be written as
> >
> > select address.address_key
> > from address, member
> > where address.address_key = address.address_key
> > and member.subclient_key = 26> >
> > Not sure if the performance would be any better though.
> >
> > byrdfarmer@hotmail.com (byrdfarmer) wrote in message news:<5e52df43.0405071126.2107f05c@posting.google.com>...
> > > In the query analysis below, I am wondering if I should add up all of
> > > the "estimated cost" values in order to determine the totl cost of
> > > this query?
> > >
> > > QUERY:
> > > ------
> > > select *
> > > from member
> > > where subclient_key = 26
> > > and address_key not in(select address_key
> > > from address
> > > where address_key in(select address_key
> > > from member where
> > > subclient_key = 26)
> > > )> > >
> > > Estimated Cost: 36262
> > > Estimated # of Rows Returned: 334577
> > >
> > > 1) informix.member: INDEX PATH
> > >
> > > Filters: informix.member.address_key != ALL <subquery>
> > >
> > > (1) Index Keys: subclient_key member_id (Serial, fragments: ALL)
> > > Lower Index Filter: informix.member.subclient_key = 26
> > >
> > > Subquery:
> > > ---------
> > > Estimated Cost: 21014
> > > Estimated # of Rows Returned: 172676
> > >
> > > 1) informix.address: INDEX PATH
> > >
> > > (1) Index Keys: address_key (Key-Only) (Serial, fragments:
> > > ALL)
> > > Lower Index Filter: informix.address.address_key = ANY
> > > <subquery>
> > >
> > > Subquery:
> > > ---------
> > > Estimated Cost: 15247
> > > Estimated # of Rows Returned: 345260
> > >
> > > 1) informix.member: INDEX PATH
> > >
> > > (1) Index Keys: subclient_key member_id (Serial,
> > > fragments: ALL)
> > > Lower Index Filter: informix.member.subclient_key = 26