Home » RDBMS Server » Performance Tuning » sql query and explain plan
sql query and explain plan [message #64696] Sun, 28 December 2003 23:26 Go to next message
Sam
Messages: 255
Registered: April 2000
Senior Member
HI all ,i am trying to tune the following query . it is taking index path for all the tables except motor table eventhough it has got indexex on it. can anybody pls explain(its in informix but i dont think it makes much difference)
SELECT Claim.sregion, Claim.sregiongrp, subcls.sclasscode, subcls.ssubclasscode, Claim.sclaimno, Claim.spolno, subcls.ssubclassname, Claim.dsdateofloss, Claim.suserid, polhead.spolstatus, motor.svehregno, Claim.scauseofloss, Claim.sliabilitycode, climasp.scompnameindex, climasi.scompnameindex, Claim.dtdateregistered, ( Sum(caEst.nnetincurred) ) As NetIncurred, motor.iseqnoitem
FROM
tbcaclaim Claim, tbebsubclass subcls, tbutpolicyheader polhead, tbcamotor motor, tbcaclientlink clilnkp, tblaclientmaster climasp, tblaclientmaster climasi, tbutclientlink clilnkI, tbcaestimate caEst

WHERE subcls.sinsurer = Claim.sinsurer AND polhead.spolno = Claim.spolno AND
motor.sclaimno = Claim.sclaimno AND motor.spolno = Claim.spolno AND
clilnkp.sclaimno = Claim.sclaimno AND clilnkp.spolno = Claim.spolno AND
climasp.sclientid = clilnkp.sclientid AND clilnkI.sobjectid = Claim.spolno AND
climasi.sclientid = clilnkI.sclientid AND clilnkI.sobjectlink = polhead.spolbustype AND
clilnkI.iseqnotrans = polhead.iseqnotrans AND clilnkI.sownerrefno = polhead.srefno AND
caEst.sclaimno = Claim.sclaimno AND caEst.ssubclasscode = subcls.ssubclasscode AND
motor.ssubclasscode = caEst.ssubclasscode AND motor.iseqnoitem = caEst.iseqnoitem AND
polhead.spolbustype = 'Policy' AND clilnkp.slinktype = 'Policyhold' AND
subcls.sportfoliocode = 'MOTOR' AND clilnkI.slinktype = 'Intermed' AND clilnkI.bservicing = 1 and
claim.dtDateRegistered between '2003-11-01 00:00:00.000' and '2003-11-30 23:59:59.000' and
clilnkp.sclientid = (select max(sclientid) from tbcaclientlink clilnkp where clilnkp.slinktype = 'Policyhold' AND clilnkp.sclaimno = Claim.sclaimno) and
polhead.iSeqNoTrans = (Select max(p.iSeqNoTrans) from tbutpolicyheader p where p.spolno = polhead.spolno and p.dspteffectivedate <= claim.dsdateofloss and p.spolbustype = polhead.spolbustype)

GROUP BY Claim.sregiongrp, Claim.sregion, subcls.sclasscode, subcls.ssubclasscode, Claim.sclaimno, Claim.spolno, subcls.ssubclassname, Claim.suserid, polhead.spolstatus, motor.svehregno, Claim.scauseofloss, Claim.sliabilitycode, Claim.dtdateregistered, climasi.scompnameindex, climasp.scompnameindex, motor.iseqnoitem, Claim.dsdateofloss
ORDER BY Claim.sregiongrp, subcls.sclasscode, subcls.ssubclasscode, Claim.sclaimno, subcls.ssubclassname
EXPLAIN PLAN
Re: sql query and explain plan [message #64697 is a reply to message #64696] Mon, 29 December 2003 00:20 Go to previous messageGo to next message
Hiren Patel
Messages: 10
Registered: May 2002
Junior Member
Pls Send Explain Plan
Re: sql query and explain plan [message #64698 is a reply to message #64697] Mon, 29 December 2003 00:25 Go to previous messageGo to next message
Sam
Messages: 255
Registered: April 2000
Senior Member
eventhough it is taking indexpath for all except one table, it took one hour still it wasnt completed.
all the tables has got the index req'd i suppose.
pls go thru and advice me
thanx in advance
1) informix.motor: SEQUENTIAL SCAN

2) informix.clilnkp: INDEX PATH

Filters: informix.clilnkp.spolno = informix.motor.spolno

(1) Index Keys: sclaimno slinktype
Lower Index Filter: (informix.clilnkp.slinktype = 'Policyhold' AND informix.clilnkp.sclaimno = informix.motor.sclaimno )
NESTED LOOP JOIN

3) informix.caest: INDEX PATH

Filters: informix.caest.ssubclasscode = informix.motor.ssubclasscode

(1) Index Keys: sclaimno iseqnoitem
Lower Index Filter: (informix.caest.sclaimno = informix.clilnkp.sclaimno AND informix.caest.iseqnoitem = informix.motor.iseqnoitem )
NESTED LOOP JOIN

4) informix.clilnki: INDEX PATH

Filters: (informix.clilnki.slinktype = 'Intermed' AND (informix.clilnki.bservicing = 1 AND informix.clilnki.sobjectlink = 'Policy' ) )

(1) Index Keys: sobjectid sclientid
Lower Index Filter: informix.clilnki.sobjectid = informix.clilnkp.spolno
NESTED LOOP JOIN

5) informix.polhead: INDEX PATH

Filters: (informix.polhead.spolno = informix.clilnki.sobjectid AND (informix.polhead.spolbustype = informix.clilnki.sobjectlink AND informix.polhead.spolbustype = 'Policy' ) )

(1) Index Keys: srefno iseqnotrans
Lower Index Filter: (informix.polhead.srefno = informix.clilnki.sownerrefno AND informix.polhead.iseqnotrans = informix.clilnki.iseqnotrans )
NESTED LOOP JOIN

6) informix.claim: INDEX PATH

Filters: (informix.claim.sclaimno = informix.clilnkp.sclaimno AND (<subquery> = informix.clilnkp.sclientid AND (<subquery> = informix.polhead.iseqnotrans AND (informix.claim.dtdateregistered >= datetime(2003-11-01 00:00:00.000) year to fraction(3) AND informix.claim.dtdateregistered <= datetime(2003-11-30 23:59:59.000) year to fraction(3) ) ) ) )

(1) Index Keys: spolno dsdateofloss
Lower Index Filter: informix.claim.spolno = informix.polhead.spolno
NESTED LOOP JOIN

7) informix.climasi: INDEX PATH

(1) Index Keys: sclientid
Lower Index Filter: informix.climasi.sclientid = informix.clilnki.sclientid
NESTED LOOP JOIN

8) informix.subcls: INDEX PATH

Filters: informix.subcls.sportfoliocode = 'MOTOR'

(1) Index Keys: sinsurer ssubclasscode
Lower Index Filter: (informix.subcls.ssubclasscode = informix.motor.ssubclasscode AND informix.subcls.sinsurer = informix.claim.sinsurer )
NESTED LOOP JOIN

9) informix.climasp: INDEX PATH

(1) Index Keys: sclientid
Lower Index Filter: informix.climasp.sclientid = informix.clilnkp.sclientid
NESTED LOOP JOIN

Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1

1) informix.clilnkp: INDEX PATH

(1) Index Keys: sclaimno slinktype
Lower Index Filter: (informix.clilnkp.slinktype = 'Policyhold' AND informix.clilnkp.sclaimno = informix.claim.sclaimno )

Subquery:
---------
Estimated Cost: 3
Estimated # of Rows Returned: 1

1) informix.p: INDEX PATH

Filters: informix.p.spolbustype = 'Policy'

(1) Index Keys: spolno stranscode dspteffectivedate (Key-First)
Lower Index Filter: informix.p.spolno = informix.polhead.spolno
Key-First Filters: (informix.p.dspteffectivedate <= informix.claim.dsdateofloss )
Re: sql query and explain plan [message #64701 is a reply to message #64698] Mon, 29 December 2003 17:39 Go to previous message
Sam
Messages: 255
Registered: April 2000
Senior Member
hi dhiren can u suggest me how can i improve the performance of the sql query
Previous Topic: statspack
Next Topic: resources on tuning
Goto Forum:
  


Current Time: Sun Sep 20 15:24:01 CDT 2020