SQEXPLAIN output
Hi,
I have the output of optimizer for the below query:
QUERY:
------
select count (distinct cc.cust_id) from subscription s, accounts a,cus_contact cc
where A.cust_id=S.cust_id and a.cust_id=cc.cust_id
AND (s.term_date < date('03-07-2000') - 2190 units day )
and (a.end_Date < (current))
and s.cust_id
not in (SELECT act.cust_id FROM subscription act, subscription exp
WHERE act.cust_id=exp.cust_id and act.term_date < date('03-07-2000') - 2190
units day AND exp.term_date > date('03-07-2000') - 2190 units day )
and s.cust_id
not in (SELECT act.cust_id
FROM subscription act, subscription exp
WHERE act.cust_id=exp.cust_id and act.state = '4' AND exp.state = '6')
and s.cust_id in
(select cust_id from subscription where s.state = '6' )
Estimated Cost: 647193920
Estimated # of Rows Returned: 1
1) informix.s: INDEX PATH
Filters: ((informix.s.cust_id != ALL <subquery> AND informix.s.state = 6 ) AND
informix.s.cust_id != ALL <subquery> )
(1) Index Keys: term_date (Serial, fragments: ALL)
Upper Index Filter: informix.s.term_date < datetime(1994-07-05) year to day
2) informix.cc: INDEX PATH
(1) Index Keys: cust_id (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.s.cust_id = informix.cc.cust_id
NESTED LOOP JOIN
3) informix.a: INDEX PATH
Filters: informix.a.end_date < CURRENT year to fraction(3)
(1) Index Keys: cust_id (Serial, fragments: ALL)
Lower Index Filter: informix.a.cust_id = informix.cc.cust_id
NESTED LOOP JOIN
4) css.subscription: AUTOINDEX PATH (First Row)
(1) Index Keys: cust_id (Key-Only)
Lower Index Filter: informix.cc.cust_id = css.subscription.cust_id
NESTED LOOP JOIN (Semi Join)
Subquery:
---------
Estimated Cost: 38396940
Estimated # of Rows Returned: 2494725
1) informix.act: INDEX PATH
(1) Index Keys: term_date (Serial, fragments: ALL)
Upper Index Filter: informix.act.term_date < datetime(1994-07-05) year to day
2) informix.exp: INDEX PATH
(1) Index Keys: term_date (Serial, fragments: ALL)
Lower Index Filter: informix.exp.term_date > datetime(1994-07-05) year to day
DYNAMIC HASH JOIN (Build Outer)
Dynamic Hash Filters: informix.act.cust_id = informix.exp.cust_id
Subquery:
---------
Estimated Cost: 489633056
Estimated # of Rows Returned: 1813601920
1) informix.act: SEQUENTIAL SCAN
Filters: informix.act.state = 4
2) informix.exp: SEQUENTIAL SCAN
Filters:
Table Scan Filters: informix.exp.state = 6
DYNAMIC HASH JOIN (Build Outer)
Dynamic Hash Filters: informix.act.cust_id = informix.exp.cust_id
Can somebody please suggest any way to improve this query?
Regards,
Kamlesh
Kamlesh
Do you have indexes on:
cust_contact.cust_id
subscription.cust_id
subscription.term_date
subscription.state
accounts.cust_id
account.end_date ?
Have the recommended update statistics been run for these tables and
columns ?
How many rows are there in each table ?
Often, rather than trying to write a single query with multiple
sub-queries, it can be more efficient (and therefore quicker) to use temp
tables to hold the results of sub-queries and then stich it back together
at the end. Of course only possible if you have control over the generation
of the SQL.
Keith
On 29 December 2014 at 13:17, KAMLESH GALLANI <kamlesh.gallani@cognizant.com
> wrote:
> Hi,
> I have the output of optimizer for the below query:
>
> QUERY:
> ------
> select count (distinct cc.cust_id) from subscription s, accounts a,> cus_contact cc
> where A.cust_id=S.cust_id and a.cust_id=cc.cust_id
> AND (s.term_date < date('03-07-2000') - 2190 units day )
> and (a.end_Date < (current))
> and s.cust_id
> not in (SELECT act.cust_id FROM subscription act, subscription exp
>
> WHERE act.cust_id=exp.cust_id and act.term_date < date('03-07-2000') - 2190
> units day AND exp.term_date > date('03-07-2000') - 2190 units day )
>
> and s.cust_id
>
> not in (SELECT act.cust_id
>
> FROM subscription act, subscription exp
>
> WHERE act.cust_id=exp.cust_id and act.state = '4' AND exp.state = '6')
>
> and s.cust_id in
>
> (select cust_id from subscription where s.state = '6' )
>
> Estimated Cost: 647193920
> Estimated # of Rows Returned: 1
>
> 1) informix.s: INDEX PATH
>
> Filters: ((informix.s.cust_id != ALL <subquery> AND informix.s.state = 6 )
> AND
> informix.s.cust_id != ALL <subquery> )
>
> (1) Index Keys: term_date (Serial, fragments: ALL)
>
> Upper Index Filter: informix.s.term_date < datetime(1994-07-05) year to day
>
> 2) informix.cc: INDEX PATH
>
> (1) Index Keys: cust_id (Key-Only) (Serial, fragments: ALL)
>
> Lower Index Filter: informix.s.cust_id = informix.cc.cust_id
> NESTED LOOP JOIN
>
> 3) informix.a: INDEX PATH
>
> Filters: informix.a.end_date < CURRENT year to fraction(3)
>
> (1) Index Keys: cust_id (Serial, fragments: ALL)
>
> Lower Index Filter: informix.a.cust_id = informix.cc.cust_id
> NESTED LOOP JOIN
>
> 4) css.subscription: AUTOINDEX PATH (First Row)
>
> (1) Index Keys: cust_id (Key-Only)
>
> Lower Index Filter: informix.cc.cust_id = css.subscription.cust_id
> NESTED LOOP JOIN (Semi Join)
>
> Subquery:
>
> ---------
>
> Estimated Cost: 38396940
>
> Estimated # of Rows Returned: 2494725
>
> 1) informix.act: INDEX PATH
>
> (1) Index Keys: term_date (Serial, fragments: ALL)
>
> Upper Index Filter: informix.act.term_date < datetime(1994-07-05) year to
> day
>
> 2) informix.exp: INDEX PATH
>
> (1) Index Keys: term_date (Serial, fragments: ALL)
>
> Lower Index Filter: informix.exp.term_date > datetime(1994-07-05) year to
> day
>
> DYNAMIC HASH JOIN (Build Outer)
>
> Dynamic Hash Filters: informix.act.cust_id = informix.exp.cust_id
>
> Subquery:
>
> ---------
>
> Estimated Cost: 489633056
>
> Estimated # of Rows Returned: 1813601920
>
> 1) informix.act: SEQUENTIAL SCAN
>
> Filters: informix.act.state = 4
>
> 2) informix.exp: SEQUENTIAL SCAN
>
> Filters:
>
> Table Scan Filters: informix.exp.state = 6
>
> DYNAMIC HASH JOIN (Build Outer)
>
> Dynamic Hash Filters: informix.act.cust_id = informix.exp.cust_id
>
> Can somebody please suggest any way to improve this query?
>
> Regards,
> Kamlesh
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec51824a8f14377050b5b3cb2
Looks like you could do with an index on subscription(cust_id). How many
rows are in that table?
What is the distribution of the "state" values in subscription? Run the
following:
Select state, count(*)
From subscription
Group by 1
Order by 1;
May want to create an index on subscription to be on (cust_id, state) to get
a key only scan, but if there are relatively few records for "state" of 4
and 6, then may want to flip the order so that it's (state, cust_id)
instead.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
KAMLESH GALLANI
Sent: Monday, December 29, 2014 6:17 AM
To: ids@iiug.org
Subject: SQEXPLAIN output [34404]
Hi,
I have the output of optimizer for the below query:
QUERY:
------
select count (distinct cc.cust_id) from subscription s, accounts a,cus_contact cc where A.cust_id=S.cust_id and a.cust_id=cc.cust_id AND
(s.term_date < date('03-07-2000') - 2190 units day ) and (a.end_Date <
(current)) and s.cust_id not in (SELECT act.cust_id FROM subscription act,
subscription exp
WHERE act.cust_id=exp.cust_id and act.term_date < date('03-07-2000') - 2190
units day AND exp.term_date > date('03-07-2000') - 2190 units day )
and s.cust_id
not in (SELECT act.cust_id
FROM subscription act, subscription exp
WHERE act.cust_id=exp.cust_id and act.state = '4' AND exp.state = '6')
and s.cust_id in
(select cust_id from subscription where s.state = '6' )
Estimated Cost: 647193920
Estimated # of Rows Returned: 1
1) informix.s: INDEX PATH
Filters: ((informix.s.cust_id != ALL <subquery> AND informix.s.state = 6 )
AND informix.s.cust_id != ALL <subquery> )
(1) Index Keys: term_date (Serial, fragments: ALL)
Upper Index Filter: informix.s.term_date < datetime(1994-07-05) year to day
2) informix.cc: INDEX PATH
(1) Index Keys: cust_id (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.s.cust_id = informix.cc.cust_id NESTED LOOP
JOIN
3) informix.a: INDEX PATH
Filters: informix.a.end_date < CURRENT year to fraction(3)
(1) Index Keys: cust_id (Serial, fragments: ALL)
Lower Index Filter: informix.a.cust_id = informix.cc.cust_id NESTED LOOP
JOIN
4) css.subscription: AUTOINDEX PATH (First Row)
(1) Index Keys: cust_id (Key-Only)
Lower Index Filter: informix.cc.cust_id = css.subscription.cust_id NESTED
LOOP JOIN (Semi Join)
Subquery:
---------
Estimated Cost: 38396940
Estimated # of Rows Returned: 2494725
1) informix.act: INDEX PATH
(1) Index Keys: term_date (Serial, fragments: ALL)
Upper Index Filter: informix.act.term_date < datetime(1994-07-05) year to
day
2) informix.exp: INDEX PATH
(1) Index Keys: term_date (Serial, fragments: ALL)
Lower Index Filter: informix.exp.term_date > datetime(1994-07-05) year to
day
DYNAMIC HASH JOIN (Build Outer)
Dynamic Hash Filters: informix.act.cust_id = informix.exp.cust_id
Subquery:
---------
Estimated Cost: 489633056
Estimated # of Rows Returned: 1813601920
1) informix.act: SEQUENTIAL SCAN
Filters: informix.act.state = 4
2) informix.exp: SEQUENTIAL SCAN
Filters:
Table Scan Filters: informix.exp.state = 6
DYNAMIC HASH JOIN (Build Outer)
Dynamic Hash Filters: informix.act.cust_id = informix.exp.cust_id
Can somebody please suggest any way to improve this query?
Regards,
Kamlesh
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.