can someone please help me interpret sqexplain.out results?
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
byrdfarmer wrote: > 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: >[results of SET EXPLAIN clipped] Have you had a chance to read the section in the Performance Guide on queries and the query optimizer? Based on your subject line, this would be a good place to start. I'm including a link to the IDS 9.4 version of the manual. You can get other versions of that manual from the IBM site. http://publib.boulder.ibm.com/epubs/pdf/ct1t9na.pdf Once you get through that section, you should be pretty comfortable with what the sqexplain.out file is showing. -- June Hunt
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
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