Home » RDBMS Server » Performance Tuning » sql query and explain plan
| sql query and explain plan [message #64696] |
Sun, 28 December 2003 23:26  |
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 #64698 is a reply to message #64697] |
Mon, 29 December 2003 00:25  |
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 )
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Oct 31 17:02:38 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
