时间：2021-07-01 10:21:17 帮助过：3人阅读
在这里我不想把该SQL的执行计划贴出来，因为我根本就没有保存^-^，而且，切入点并不是从原SQL的执行计划开始。
开始分析该SQL的逻辑：
表import_pay_confirm是从前台页面导入的表，有四个字段，在SQL的最后与t1进行关联。因此我采用union all将该语句改写了，这样就能帮助优化器进行查询改写，视图合并之类的。这是第一个改写的地方
第二个地方是t1中的选择列：
decode((select count(*)
from prem_payment_detail_target
where p.c_paid_id = C_PAID_ID),
0,
‘否‘,
‘是‘) as selletStatus,
改写后的该选择列的表达式为decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus，因为只是作一个判断，完全可以直接对prem_payment_detail_target表的字段c_paid_id进行判断，这样可以减少对该表的重复访问
改写后的SQL相当长，但是逻辑要简单很多，而且速度快很多，因此也证明了，SQL写的长不一定性能不好，效率不高，SQL短不代表逻辑清晰、性能好，下面是SQL语句与执行计划：
select count(*) from (select distinct t1.* from (select p.c_paid_id as id, p.c_branch_name as organName, p.c_dept_name as daptName, d.project_name as itemName, p.c_Insurance_Com_Name as pryerName, nvl(p.n_Current_Premium, 0) as commission, decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType, p.c_Apply_Code as allInsuranceCode, nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode, p.c_Endorsement_Code as endorsenentCode, decode(p.c_endorsement_code, null, nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘), to_char(p.sign_time, ‘yyyy-mm-dd‘)), to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate, decode(p.c_endorsement_code, null, nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘), to_char(p.start_date, ‘yyyy-mm-dd‘)), to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate, decode(p.c_endorsement_code, null, nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘), to_char(p.end_date, ‘yyyy-mm-dd‘)), to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate, to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate, decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus, se.C_PAYMENT_CODE as settlementCode, p.c_is_paid as isSettled, p.C_PERIOD as periodno, p.C_INSURANCE_ID as insuranceId, nvl(p.c_insurant_name, lcc.customer_name) as insuredName from paid_info_target p, lccont_target lcc, d_projects d, prem_payment_detail_target se, insurance_policy_target ipt where p.c_unite_policy = lcc.contno(+) and p.c_project_code = d.project_code(+) and p.C_PAID_ID = se.C_PAID_ID(+) and (p.c_unite_policy = ipt.contno(+) and p.c_endorsement_code = ipt.mli_insurance_endorsement_no(+)) and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘ and p.c_dept_id = ‘JT015200107‘ and p.c_branch_code = ‘JT0152001‘ and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘ order by lcc.INSURANCE_POLICY_NO desc) t1, import_pay_confirm t2 where t2.c_bath_no = 1 and t1.allInsuranceCode = t2.c_apply_no and t2.c_import_by = ‘dx.xie‘ union all select distinct t1.* from (select p.c_paid_id as id, p.c_branch_name as organName, p.c_dept_name as daptName, d.project_name as itemName, p.c_Insurance_Com_Name as pryerName, nvl(p.n_Current_Premium, 0) as commission, decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType, p.c_Apply_Code as allInsuranceCode, nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode, p.c_Endorsement_Code as endorsenentCode, decode(p.c_endorsement_code, null, nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘), to_char(p.sign_time, ‘yyyy-mm-dd‘)), to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate, decode(p.c_endorsement_code, null, nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘), to_char(p.start_date, ‘yyyy-mm-dd‘)), to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate, decode(p.c_endorsement_code, null, nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘), to_char(p.end_date, ‘yyyy-mm-dd‘)), to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate, to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate, decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus, se.C_PAYMENT_CODE as settlementCode, p.c_is_paid as isSettled, p.C_PERIOD as periodno, p.C_INSURANCE_ID as insuranceId, nvl(p.c_insurant_name, lcc.customer_name) as insuredName from paid_info_target p, lccont_target lcc, d_projects d, prem_payment_detail_target se, insurance_policy_target ipt where p.c_unite_policy = lcc.contno(+) and p.c_project_code = d.project_code(+) and p.C_PAID_ID = se.C_PAID_ID(+) and (p.c_unite_policy = ipt.contno(+) and p.c_endorsement_code = ipt.mli_insurance_endorsement_no(+)) and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘ and p.c_dept_id = ‘JT015200107‘ and p.c_branch_code = ‘JT0152001‘ and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘ order by lcc.INSURANCE_POLICY_NO desc) t1, import_pay_confirm t2 where t2.c_bath_no = 1 and t1.insuranceCode = t2.c_policy_no and t2.c_import_by = ‘dx.xie‘ and t2.c_apply_no is null union all select distinct t1.* from (select p.c_paid_id as id, p.c_branch_name as organName, p.c_dept_name as daptName, d.project_name as itemName, p.c_Insurance_Com_Name as pryerName, nvl(p.n_Current_Premium, 0) as commission, decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType, p.c_Apply_Code as allInsuranceCode, nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode, p.c_Endorsement_Code as endorsenentCode, decode(p.c_endorsement_code, null, nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘), to_char(p.sign_time, ‘yyyy-mm-dd‘)), to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate, decode(p.c_endorsement_code, null, nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘), to_char(p.start_date, ‘yyyy-mm-dd‘)), to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate, decode(p.c_endorsement_code, null, nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘), to_char(p.end_date, ‘yyyy-mm-dd‘)), to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate, to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate, decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus, se.C_PAYMENT_CODE as settlementCode, p.c_is_paid as isSettled, p.C_PERIOD as periodno, p.C_INSURANCE_ID as insuranceId, nvl(p.c_insurant_name, lcc.customer_name) as insuredName from paid_info_target p, lccont_target lcc, d_projects d, prem_payment_detail_target se, insurance_policy_target ipt where p.c_unite_policy = lcc.contno(+) and p.c_project_code = d.project_code(+) and p.C_PAID_ID = se.C_PAID_ID(+) and (p.c_unite_policy = ipt.contno(+) and p.c_endorsement_code = ipt.mli_insurance_endorsement_no(+)) and p.C_INSURANCE_COM_CODE = ‘0001300049.00158‘ and p.c_dept_id = ‘JT015200107‘ and p.c_branch_code = ‘JT0152001‘ and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘ order by lcc.INSURANCE_POLICY_NO desc) t1, import_pay_confirm t2 where t2.c_bath_no = 1 and t1.endorsenentCode = t2.C_ENDORSEMENT_CODE and t2.c_import_by = ‘dx.xie‘ and t2.c_policy_no is null and t2.c_apply_no is null union all select distinct t1.* from (select p.c_paid_id as id, p.c_branch_name as organName, p.c_dept_name as daptName, d.project_name as itemName, p.c_Insurance_Com_Name as pryerName, nvl(p.n_Current_Premium, 0) as commission, decode(p.c_Endorsement_Code, null, ‘保单‘, ‘批单‘) as policyType, p.c_Apply_Code as allInsuranceCode, nvl(p.c_insurance_code, lcc.INSURANCE_POLICY_NO) as insuranceCode, p.c_Endorsement_Code as endorsenentCode, decode(p.c_endorsement_code, null, nvl(to_char(lcc.sign_time, ‘yyyy-mm-dd‘), to_char(p.sign_time, ‘yyyy-mm-dd‘)), to_char(ipt.effective_date, ‘yyyy-mm-dd‘)) as signDate, decode(p.c_endorsement_code, null, nvl(to_char(lcc.start_date, ‘yyyy-mm-dd‘), to_char(p.start_date, ‘yyyy-mm-dd‘)), to_char(ipt.start_date, ‘yyyy-mm-dd‘)) as starDate, decode(p.c_endorsement_code, null, nvl(to_char(lcc.end_date, ‘yyyy-mm-dd‘), to_char(p.end_date, ‘yyyy-mm-dd‘)), to_char(ipt.end_date, ‘yyyy-mm-dd‘)) as endDate, to_char(p.d_ddate, ‘yyyy-mm-dd‘) as ddate, decode(se.c_paid_id, null, ‘否‘, ‘是‘) as selletStatus, se.C_PAYMENT_CODE as settlementCode, p.c_is_paid as isSettled, p.C_PERIOD as periodno, p.C_INSURANCE_ID as insuranceId, nvl(p.c_insurant_name, lcc.customer_name) as insuredName from paid_info_target p, lccont_target lcc, d_projects d, prem_payment_detail_target se, insurance_policy_target ipt where p.c_unite_policy = lcc.contno(+) and p.c_project_code = d.project_code(+) and p.C_PAID_ID = se.C_PAID_ID(+) and (p.c_unite_policy = ipt.contno(+) and p.c_endorsement_code = ipt.mli_insurance_endorsement_no(+)) and p.C_INSURANCE_COM_CODE = 0001300049.00158 and p.c_dept_id = ‘JT015200107‘ and p.c_branch_code = ‘JT0152001‘ and p.c_system_name like ‘%‘ || ‘旅游保险网‘ || ‘%‘ order by lcc.INSURANCE_POLICY_NO desc) t1, import_pay_confirm t2 where t2.c_bath_no = 1 and t1.insuranceId = t2.C_INSURANCE_ID and t2.c_import_by = ‘dx.xie‘ and t2.c_policy_no is null and t2.c_apply_no is null and t2.C_ENDORSEMENT_CODE is null); ------------------------------------- SQL_ID brut29q5yb2j5, child number 1 ------------------------------------- Plan hash value: 3456365982 ------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.79 | 142K| | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.79 | 142K| | | | | 2 | VIEW | | 1 | 4 | 165 |00:00:00.79 | 142K| | | | | 3 | UNION-ALL | | 1 | | 165 |00:00:00.79 | 142K| | | | | 4 | HASH UNIQUE | | 1 | 1 | 165 |00:00:00.02 | 3274 | 698K| 698K| 636K (0)| | 5 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.02 | 2942 | | | | | 6 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.01 | 2445 | | | | | 7 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.01 | 2441 | | | | | 8 | NESTED LOOPS OUTER | | 1 | 1 | 165 |00:00:00.01 | 1954 | | | | | 9 | NESTED LOOPS | | 1 | 1 | 165 |00:00:00.01 | 1622 | | | | |* 10 | TABLE ACCESS FULL | IMPORT_PAY_CONFIRM | 1 | 181 | 164 |00:00:00.01 | 374 | | | | |* 11 | TABLE ACCESS BY INDEX ROWID | PAID_INFO_TARGET | 164 | 1 | 165 |00:00:00.01 | 1248 | | | | |* 12 | INDEX RANGE SCAN | INDEX_PAID_5 | 164 | 1 | 990 |00:00:00.01 | 496 | | | | | 13 | TABLE ACCESS BY INDEX ROWID | D_PROJECTS | 165 | 1 | 165 |00:00:00.01 | 332 | | | | |* 14 | INDEX RANGE SCAN | INDEX_D_PROJECTS_1 | 165 | 1 | 165 |00:00:00.01 | 167 | | | | | 15 | TABLE ACCESS BY INDEX ROWID | LCCONT_TARGET | 165 | 1 | 153 |00:00:00.01 | 487 | | | | |* 16 | INDEX RANGE SCAN | INDEX_LCCONT_CONTNO | 165 | 1 | 153 |00:00:00.01 | 334 | | | | |* 17 | TABLE ACCESS BY INDEX ROWID | INSURANCE_POLICY_TARGET | 165 | 1 | 1 |00:00:00.01 | 4 | | | | |* 18 | INDEX RANGE SCAN | INDEX_INSUE_POLICY_1 | 165 | 1 | 1 |00:00:00.01 | 3 | | | | | 19 | TABLE ACCESS BY INDEX ROWID | PREM_PAYMENT_DETAIL_TARGET | 165 | 1 | 165 |00:00:00.01 | 497 | | | | |* 20 | INDEX RANGE SCAN | INDEX_PREM_DETAIL_1 | 165 | 1 | 165 |00:00:00.01