SQL using multiple the union all query optimization problem (please god help)-CodePudding

Select distinct PM. Policyno,
Decode (pr) plancode, '0000', pr, riskcode, pr. Plancode) as riskcode,
Pr. Startdate,
Pr endDate,
PM. Cancelind,
PM. RenewedInd,
PM. SurrenderInd,
PM. Validind,
Pm.com panycode
The from GuPolicyMain PM, GuPolicyRisk pr
Where PM. PolicyNo=pr. PolicyNo
AND (PM) coinsInd='0')
AND (PM) cancelInd='0')
AND (PM) renewedInd='0')
AND (PM) surrenderInd='0')
AND (pr) validInd='1')
And pm.com panyCode not in
(' 010601 ', '0106010097', '01060101', '0106010197')
And to_char (pr) enddate, 'mm - dd yyyy - hh: mm: dd') & lt;=
To_char (' 2017-01-31 23:59:59)
And to_char (pr) enddate, 'mm - dd yyyy - hh: mm: dd') & gt;=
To_char (' 2017-01-01 00:00:00)
And PM. BusinessType not in (' 2 ', '3')
And PM. StandardInd is null
And the exists (select riskcode
The from ggriskconfigvalue
Where configcode='RENEWAL_NEWPROCESSIND'
And validInd='1'
And riskcode=pr. Riskcode)
And pr. Plancode='0000'
Union all
Select distinct PM. Policyno,
Decode (pr) plancode, '0000', pr, riskcode, pr. Plancode) as riskcode,
Pr. Startdate,
Pr endDate,
PM. Cancelind,
PM. RenewedInd,
PM. SurrenderInd,
PM. Validind,
Pm.com panycode
The from GuPolicyMain PM, GuPolicyRisk pr
Where PM. PolicyNo=pr. PolicyNo
AND (PM) coinsInd='0')
AND (PM) cancelInd='0')
AND (PM) renewedInd='0')
AND (PM) surrenderInd='0')
AND (pr) validInd='1')
And pm.com panyCode not in
(' 010601 ', '0106010097', '01060101', '0106010197')
And to_char (pr) enddate, 'mm - dd yyyy - hh: mm: dd') & lt;=
To_char (' 2017-01-31 23:59:59)
And to_char (pr) enddate, 'mm - dd yyyy - hh: mm: dd') & gt;=
To_char (' 2017-01-01 00:00:00)
And PM. BusinessType not in (' 2 ', '3')
And PM. StandardInd is null
And pr. Plancode & lt;> '0000'
And the exists (select riskcode
The from ggriskconfigvalue
Where configcode='RENEWAL_NEWPROCESSIND'
And validInd='1'
And riskcode=pr. PlanCode)
Union all
Select distinct PM. Policyno,
Decode (pr) plancode, '0000', pr, riskcode, pr. Plancode) as riskcode,
Pr. Startdate,
Pr endDate,
PM. Cancelind,
PM. RenewedInd,
PM. SurrenderInd,
PM. Validind,
Pm.com panycode
The from GuPolicyMain PM, GuPolicyRisk pr
Where PM. PolicyNo=pr. PolicyNo
AND (PM) coinsInd='0')
AND (PM) cancelInd='0')
AND (PM) renewedInd='0')
AND (PM) surrenderInd='0')
AND (pr) validInd='1')
And pm.com panyCode in
(' 010601 ', '0106010097', '01060101', '0106010197')
And to_char (pr) enddate, 'mm - dd yyyy - hh: mm: dd') & lt;=
To_char (' 2017-02-28 23:59:59)
And to_char (pr) enddate, 'mm - dd yyyy - hh: mm: dd') & gt;=
To_char (' 2017-02-01 00:00:00)
And PM. BusinessType not in (' 2 ', '3')
And PM. StandardInd is null
And the exists (select riskcode
The from ggriskconfigvalue
Where configcode='RENEWAL_NEWPROCESSIND'
And validInd='1'
And riskcode=pr. Riskcode)
And pr. Plancode='0000'
Union all
Select distinct PM. Policyno,
Decode (pr) plancode, '0000', pr, riskcode, pr. Plancode) as riskcode,
Pr. Startdate,
Pr endDate,
PM. Cancelind,
PM. RenewedInd,
PM. SurrenderInd,
PM. Validind,
Pm.com panycode
The from GuPolicyMain PM, GuPolicyRisk pr
Where PM. PolicyNo=pr. PolicyNo
AND (PM) coinsInd='0')
AND (PM) cancelInd='0')
AND (PM) renewedInd='0')
AND (PM) surrenderInd='0')
AND (pr) validInd='1')
And pm.com panyCode in
(' 010601 ', '0106010097', '01060101', '0106010197')
And to_char (pr) enddate, 'mm - dd yyyy - hh: mm: dd') & lt;=
To_char (' 2017-02-28 23:59:59)
And to_char (pr) enddate, 'mm - dd yyyy - hh: mm: dd') & gt;=
To_char (' 2017-02-01 00:00:00)
And PM. BusinessType not in (' 2 ', '3')
And PM. StandardInd is null
And pr. Plancode & lt;> '0000'
And the exists (select riskcode
The from ggriskconfigvalue
Where configcode='RENEWAL_NEWPROCESSIND'
And validInd='1'
And riskcode=pr. PlanCode)
The order by companycode, riskCode, policyno

CodePudding user response:

Please help me a great god see

CodePudding user response:

 select distinct PM. Policyno, 
Decode (pr) plancode, '0000', pr, riskcode, pr. Plancode) as riskcode, 
Pr. Startdate, 
Pr endDate, 
PM. Cancelind, 
PM. RenewedInd, 
PM. SurrenderInd, 
PM. Validind, 
Pm.com panycode 
The from GuPolicyMain PM, GuPolicyRisk pr 
Where PM. PolicyNo=pr. PolicyNo 
AND (PM) coinsInd='0') 
AND (PM) cancelInd='0') 
AND (PM) renewedInd='0') 
AND (PM) surrenderInd='0') 
AND (pr) validInd='1') 
And pm.com panyCode not in 
(' 010601 ', '0106010097', '01060101', '0106010197') 
And to_char (pr) enddate, 'mm - dd yyyy - hh: mm: dd') & lt;=
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull