OB一哥们找我优化条SQL，反馈在OceanBase执行时间很慢需要 3184s才能出结果，安排。
原SQL：
select M.POLICY_CHARGE_ID as policyChargeId, M.POLICY_NO as policyNo, M.ENDORSE_NO as endorseNo, (select ec.entity_name from ENTITY_CODE ec where ec.parent_code = 'BA36' and ec.entity_code = M.line) as line,--1 (select o.name from iforganization o where o.code = M.DEPTGRP_CODE and o.parent_id = (select z.org_id from iforganization z where z.code = M.BRANCH_CODE)) as deptgrpCode, (select o.name from iforganization o where o.code = M.DEPT_CODE and o.parent_id = (select z.org_id from iforganization z where z.code = M.DEPTGRP_CODE and z.parent_id = (select i.org_id from iforganization i where i.code = M.BRANCH_CODE))) as deptCode, (select e.name from ifemployee e where e.code = M.HANDLER_CODE and e.unit_code = M.Branch_Code) as handlerCode, (SELECT e.entity_name FROM entity_code e WHERE e.entity_code = M.PRODUCTGRP_CODE and e.top_code = 'BA10' AND e.parent_code = '1') productgrpName, M.DATASOURCE as DATASOURCE, M.PRODUCTGRP_CODE as productgrpCode, (select c.entity_name from entity_code c where c.top_code = 'BA53' and c.entity_code = M.CHANNELCODE) as channelcode, M.MID_CHANNELCODE as midChannelcode, M.INSURE_WAY as insureWay, M.AGENT_CODE as agentCode, M.AGENT_NAME as agentName, M.THE4S_CODE as the4sCode, (select j.entity_name from entity_code j where j.top_code = 'BA29' and j.entity_code = M.THE4S_CODE) as the4sName, M.INSURED_NAME as insuredName, M.APPLICANT_NAME as applicantName, M.START_DATE as startDate, M.END_DATE as endDate, M.ISSUE_DATE as issueDate, M.SUBPREMIUM as subpremium, M.LICENSENO as licenseno, (select u.entity_name from entity_code u where u.top_code = 'BA28' and u.entity_code = M.USETYPE) as usetype, M.VEHICLECLAIMTYPE as vehicleclaimtype, M.ADDVALUETAX as addvaluetax, M.AMOUNT_TYPE_I as amountTypeI, M.AMOUNT_TYPE_II as amountTypeII, M.AMOUNT_TYPE_III as amountTypeIII, M.AMOUNT_TYPE_IV as amountTypeIV, M.AMOUNT_TYPE_V as amountTypeV, M.AMOUNT_OVERALL as amountOverall, M.AMOUNT_OVERALL_TOTAL as amountOverallTotal, M.AMOUNT_OVERALL_BRANCH as amountOverallBranch, M.AMOUNT_OVERALL_DEPTGRP as amountOverallDeptgrp from CC_POLICY_CHARGE M where 1 = 1 AND M.UNIQUE_CODE LIKE '3010100%' AND M.LINE = '1' AND M.CHANNELCODE IN ('C001', 'C002', 'C003', 'C004', 'C005', 'C006', 'C007', 'C008','C009','C010','CAND','C012','C013','C014','C015') AND M.INSURE_WAY IN ('11','12','14','16','17','18','21', '22','23','25','27','28','29','31','32','33','36','37','38','39','40', '19','41','42','43','44','45','46','47','51') AND M.BRANCH_CODE = '3010100' AND ROWNUM <= 500000;
原SQL执行计划：
=============================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | --------------------------------------------------------------- |0 |SUBPLAN FILTER | |1938 |9646000| |1 | TABLE SCAN |M(UNIQUE_CODE) |1938 |7260012| |2 | TABLE SCAN |EC(UNI_ENTITY_CODE_02) |1 |137 | |3 | SUBPLAN FILTER | |1 |274 | |4 | TABLE SCAN |O(IND_IFORGANIZATION_1)|1 |229 | |5 | TABLE SCAN |Z(IND_IFORGANIZATION_2)|4 |46 | |6 | SUBPLAN FILTER | |1 |502 | |7 | TABLE SCAN |O(IND_IFORGANIZATION_1)|1 |229 | |8 | SUBPLAN FILTER| |1 |274 | |9 | TABLE SCAN |Z(IND_IFORGANIZATION_1)|1 |229 | |10| TABLE SCAN |I(IND_IFORGANIZATION_2)|4 |46 | |11| TABLE SCAN |E(IDX_IFEMPLOYEE_01) |1 |92 | |12| TABLE SCAN |E(UNI_ENTITY_CODE_01) |1 |92 | |13| TABLE SCAN |C(UNI_ENTITY_CODE_01) |1 |92 | |14| TABLE SCAN |J(UNI_ENTITY_CODE_01) |1 |92 | |15| TABLE SCAN |U(UNI_ENTITY_CODE_01) |1 |92 | =============================================================== Outputs & filters: ------------------------------------- 0 - output([M.POLICY_CHARGE_ID], [M.POLICY_NO], [M.ENDORSE_NO], [?], [subquery(2)], [subquery(3)], [subquery(4)], [subquery(5)], [M.DATASOURCE], [M.PRODUCTGRP_CODE], [subquery(6)], [M.MID_CHANNELCODE], [M.INSURE_WAY], [M.AGENT_CODE], [M.AGENT_NAME], [M.THE4S_CODE], [subquery(7)], [M.INSURED_NAME], [M.APPLICANT_NAME], [M.START_DATE], [M.END_DATE], [M.ISSUE_DATE], [M.SUBPREMIUM], [M.LICENSENO], [subquery(8)], [M.VEHICLECLAIMTYPE], [M.ADDVALUETAX], [M.AMOUNT_TYPE_I], [M.AMOUNT_TYPE_II], [M.AMOUNT_TYPE_III], [M.AMOUNT_TYPE_IV], [M.AMOUNT_TYPE_V], [M.AMOUNT_OVERALL], [M.AMOUNT_OVERALL_TOTAL], [M.AMOUNT_OVERALL_BRANCH], [M.AMOUNT_OVERALL_DEPTGRP]), filter(nil), exec_params_([M.DEPTGRP_CODE], [M.BRANCH_CODE], [M.DEPT_CODE], [M.DEPTGRP_CODE], [M.BRANCH_CODE], [M.HANDLER_CODE], [M.PRODUCTGRP_CODE], [M.CHANNELCODE], [M.THE4S_CODE], [M.USETYPE]), onetime_exprs_([subquery(1)]), init_plan_idxs_(nil) 1 - output([M.CHANNELCODE], [M.INSURE_WAY], [M.BRANCH_CODE], [M.POLICY_CHARGE_ID], [M.POLICY_NO], [M.ENDORSE_NO], [M.DEPTGRP_CODE], [M.DEPT_CODE], [M.HANDLER_CODE], [M.PRODUCTGRP_CODE], [M.DATASOURCE], [M.MID_CHANNELCODE], [M.AGENT_CODE], [M.AGENT_NAME], [M.THE4S_CODE], [M.INSURED_NAME], [M.APPLICANT_NAME], [M.START_DATE], [M.END_DATE], [M.ISSUE_DATE], [M.SUBPREMIUM], [M.LICENSENO], [M.USETYPE], [M.VEHICLECLAIMTYPE], [M.ADDVALUETAX], [M.AMOUNT_TYPE_I], [M.AMOUNT_TYPE_II], [M.AMOUNT_TYPE_III], [M.AMOUNT_TYPE_IV], [M.AMOUNT_TYPE_V], [M.AMOUNT_OVERALL], [M.AMOUNT_OVERALL_TOTAL], [M.AMOUNT_OVERALL_BRANCH], [M.AMOUNT_OVERALL_DEPTGRP]), filter([M.BRANCH_CODE = ?], [M.LINE = ?], [M.CHANNELCODE IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)], [M.INSURE_WAY IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]), access([M.LINE], [M.CHANNELCODE], [M.INSURE_WAY], [M.BRANCH_CODE], [M.POLICY_CHARGE_ID], [M.POLICY_NO], [M.ENDORSE_NO], [M.DEPTGRP_CODE], [M.DEPT_CODE], [M.HANDLER_CODE], [M.PRODUCTGRP_CODE], [M.DATASOURCE], [M.MID_CHANNELCODE], [M.AGENT_CODE], [M.AGENT_NAME], [M.THE4S_CODE], [M.INSURED_NAME], [M.APPLICANT_NAME], [M.START_DATE], [M.END_DATE], [M.ISSUE_DATE], [M.SUBPREMIUM], [M.LICENSENO], [M.USETYPE], [M.VEHICLECLAIMTYPE], [M.ADDVALUETAX], [M.AMOUNT_TYPE_I], [M.AMOUNT_TYPE_II], [M.AMOUNT_TYPE_III], [M.AMOUNT_TYPE_IV], [M.AMOUNT_TYPE_V], [M.AMOUNT_OVERALL], [M.AMOUNT_OVERALL_TOTAL], [M.AMOUNT_OVERALL_BRANCH], [M.AMOUNT_OVERALL_DEPTGRP]), partitions(p0), limit(?), offset(nil) 2 - output([EC.ENTITY_NAME]), filter([EC.ENTITY_CODE = ?]), access([EC.ENTITY_CODE], [EC.ENTITY_NAME]), partitions(p0) 3 - output([O.NAME]), filter(nil), exec_params_(nil), onetime_exprs_([subquery(1)]), init_plan_idxs_(nil) 4 - output([O.NAME]), filter([O.CODE = ?]), access([O.CODE], [O.NAME]), partitions(p0) 5 - output([Z.ORG_ID]), filter(nil), access([Z.ORG_ID]), partitions(p0) 6 - output([O.NAME]), filter(nil), exec_params_(nil), onetime_exprs_([subquery(1)]), init_plan_idxs_(nil) 7 - output([O.NAME]), filter([O.CODE = ?]), access([O.CODE], [O.NAME]), partitions(p0) 8 - output([Z.ORG_ID]), filter(nil), exec_params_(nil), onetime_exprs_([subquery(1)]), init_plan_idxs_(nil) 9 - output([Z.ORG_ID]), filter([Z.CODE = ?]), access([Z.CODE], [Z.ORG_ID]), partitions(p0) 10 - output([I.ORG_ID]), filter(nil), access([I.ORG_ID]), partitions(p0) 11 - output([E.NAME]), filter(nil), access([E.NAME]), partitions(p0) 12 - output([E.ENTITY_NAME]), filter([E.PARENT_CODE = ?]), access([E.PARENT_CODE], [E.ENTITY_NAME]), partitions(p0) 13 - output([C.ENTITY_NAME]), filter(nil), access([C.ENTITY_NAME]), partitions(p0) 14 - output([J.ENTITY_NAME]), filter(nil), access([J.ENTITY_NAME]), partitions(p0) 15 - output([U.ENTITY_NAME]), filter(nil), access([U.ENTITY_NAME]), partitions(p0)
执行时间：
其实刚拿到SQL大致看了一遍就知道哪里慢（OB的执行计划其实我也看不懂），标量子查询过多导致的filter。
主表 CC_POLICY_CHARGE M 共430多万行数据，只取50W出来（ROWNUM <= 500000）。
在OB上跑要 3184S（50多分钟） 出结果，而在ORACLE 只需要 1010s 就能跑出结果，可见ORACLE的CBO优化器是多强大，太顶了。
像这种SQL要优化只能等价改写（把标量子查询改成 left join 干掉 filter），无其他办法：
等价改写SQL：
SELECT M.POLICY_CHARGE_ID AS policyChargeId, M.POLICY_NO AS policyNo, M.ENDORSE_NO AS endorseNo, EC.entity_name AS line, O1.name AS deptgrpCode, O2.name AS deptCode, E.name AS handlerCode, EC2.entity_name AS productgrpName, M.DATASOURCE AS DATASOURCE, M.PRODUCTGRP_CODE AS productgrpCode, EC3.entity_name AS channelcode, M.MID_CHANNELCODE AS midChannelcode, M.INSURE_WAY AS insureWay, M.AGENT_CODE AS agentCode, M.AGENT_NAME AS agentName, M.THE4S_CODE AS the4sCode, EC4.entity_name AS the4sName, M.INSURED_NAME AS insuredName, M.APPLICANT_NAME AS applicantName, M.START_DATE AS startDate, M.END_DATE AS endDate, M.ISSUE_DATE AS issueDate, M.SUBPREMIUM AS subpremium, M.LICENSENO AS licenseno, EC5.entity_name AS usetype, M.VEHICLECLAIMTYPE AS vehicleclaimtype, M.ADDVALUETAX AS addvaluetax, M.AMOUNT_TYPE_I AS amountTypeI, M.AMOUNT_TYPE_II AS amountTypeII, M.AMOUNT_TYPE_III AS amountTypeIII, M.AMOUNT_TYPE_IV AS amountTypeIV, M.AMOUNT_TYPE_V AS amountTypeV, M.AMOUNT_OVERALL AS amountOverall, M.AMOUNT_OVERALL_TOTAL AS amountOverallTotal, M.AMOUNT_OVERALL_BRANCH AS amountOverallBranch, M.AMOUNT_OVERALL_DEPTGRP AS amountOverallDeptgrp FROM CC_POLICY_CHARGE M LEFT JOIN ENTITY_CODE EC ON EC.parent_code = 'BA36' AND EC.entity_code = M.line LEFT JOIN iforganization O1 ON O1.code = M.DEPTGRP_CODE LEFT JOIN iforganization O1_PARENT ON O1_PARENT.code = M.BRANCH_CODE AND O1_PARENT.org_id = O1.parent_id LEFT JOIN iforganization O2 ON O2.code = M.DEPT_CODE LEFT JOIN iforganization O2_PARENT ON O2_PARENT.code = M.DEPTGRP_CODE AND O2_PARENT.org_id = O2.parent_id LEFT JOIN iforganization O2_GRANDPARENT ON O2_GRANDPARENT.code = M.BRANCH_CODE AND O2_GRANDPARENT.org_id = O2_PARENT.parent_id LEFT JOIN ifemployee E ON E.code = M.HANDLER_CODE AND E.unit_code = M.Branch_Code LEFT JOIN entity_code EC2 ON EC2.entity_code = M.PRODUCTGRP_CODE AND EC2.top_code = 'BA10' AND EC2.parent_code = '1' LEFT JOIN entity_code EC3 ON EC3.top_code = 'BA53' AND EC3.entity_code = M.CHANNELCODE LEFT JOIN entity_code EC4 ON EC4.top_code = 'BA29' AND EC4.entity_code = M.THE4S_CODE LEFT JOIN entity_code EC5 ON EC5.top_code = 'BA28' AND EC5.entity_code = M.USETYPE WHERE 1 = 1 AND M.UNIQUE_CODE LIKE '3010100%' AND M.LINE = '1' AND M.CHANNELCODE IN ('C001', 'C002', 'C003', 'C004', 'C005', 'C006', 'C007', 'C008','C009','C010','CAND','C012','C013','C014','C015') AND M.INSURE_WAY IN ('11','12','14','16','17','18','21', '22','23','25','27','28','29','31','32','33','36','37','38','39','40', '19','41','42','43','44','45','46','47','51') AND M.BRANCH_CODE = '3010100' AND ROWNUM <= 500000；
改写后的执行计划：
============================================================================================= |ID|OPERATOR |NAME |EST. ROWS|COST | --------------------------------------------------------------------------------------------- |0 |LIMIT | |34690 |7739035| |1 | MERGE OUTER JOIN | |34690 |7738513| |2 | HASH RIGHT OUTER JOIN | |34690 |7737851| |3 | TABLE SCAN |EC2(UNI_ENTITY_CODE_02) |1 |183 | |4 | HASH RIGHT OUTER JOIN | |34690 |7715121| |5 | TABLE SCAN |EC3(INDEX_ENTITY_TOPCODE) |16 |183 | |6 | HASH RIGHT OUTER JOIN | |34690 |7684751| |7 | TABLE SCAN |EC5(INDEX_ENTITY_TOPCODE) |32 |184 | |8 | HASH RIGHT OUTER JOIN | |34690 |7654365| |9 | TABLE SCAN |EC4(INDEX_ENTITY_TOPCODE) |2 |137 | |10| HASH RIGHT OUTER JOIN | |34690 |7631719| |11| TABLE SCAN |O2_GRANDPARENT(IND_IFORGANIZATION_2)|1 |46 | |12| HASH RIGHT OUTER JOIN | |34690 |7608424| |13| TABLE SCAN |O1_PARENT(IND_IFORGANIZATION_2) |1 |46 | |14| HASH RIGHT OUTER JOIN | |34690 |7585128| |15| TABLE SCAN |O2_PARENT |65935 |25504 | |16| HASH OUTER JOIN | |34690 |7482018| |17| HASH OUTER JOIN | |9764 |7387204| |18| NESTED-LOOP OUTER JOIN| |2748 |7311954| |19| SUBPLAN SCAN |VIEW2 |1938 |7260041| |20| TABLE SCAN |M(UNIQUE_CODE) |1938 |7260012| |21| TABLE SCAN |E(IDX_IFEMPLOYEE_01) |1 |26 | |22| TABLE SCAN |O1 |65935 |25504 | |23| TABLE SCAN |O2 |65935 |25504 | |24| TABLE SCAN |EC(UNI_ENTITY_CODE_02) |1 |137 | ============================================================================================= Outputs & filters: ------------------------------------- 0 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [EC.ENTITY_NAME], [O1.NAME], [O2.NAME], [E.NAME], [EC2.ENTITY_NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [EC3.ENTITY_NAME], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [EC4.ENTITY_NAME], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [EC5.ENTITY_NAME], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP]), filter(nil), limit(?), offset(nil) 1 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [EC.ENTITY_NAME], [O1.NAME], [O2.NAME], [E.NAME], [EC2.ENTITY_NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [EC3.ENTITY_NAME], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [EC4.ENTITY_NAME], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [EC5.ENTITY_NAME], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP]), filter(nil), equal_conds([EC.ENTITY_CODE = VIEW2.M.LINE]), other_conds(nil) 2 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [O1.NAME], [O2.NAME], [E.NAME], [EC2.ENTITY_NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [EC3.ENTITY_NAME], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [EC4.ENTITY_NAME], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [EC5.ENTITY_NAME], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE]), filter(nil), equal_conds([EC2.ENTITY_CODE = VIEW2.M.PRODUCTGRP_CODE]), other_conds(nil) 3 - output([EC2.ENTITY_CODE], [EC2.ENTITY_NAME]), filter([EC2.TOP_CODE = ?]), access([EC2.ENTITY_CODE], [EC2.TOP_CODE], [EC2.ENTITY_NAME]), partitions(p0) 4 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [O1.NAME], [O2.NAME], [E.NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [EC3.ENTITY_NAME], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [EC4.ENTITY_NAME], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [EC5.ENTITY_NAME], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE]), filter(nil), equal_conds([EC3.ENTITY_CODE = VIEW2.M.CHANNELCODE]), other_conds(nil) 5 - output([EC3.ENTITY_CODE], [EC3.ENTITY_NAME]), filter(nil), access([EC3.ENTITY_CODE], [EC3.ENTITY_NAME]), partitions(p0) 6 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [O1.NAME], [O2.NAME], [E.NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [EC4.ENTITY_NAME], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [EC5.ENTITY_NAME], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE], [VIEW2.M.CHANNELCODE]), filter(nil), equal_conds([EC5.ENTITY_CODE = VIEW2.M.USETYPE]), other_conds(nil) 7 - output([EC5.ENTITY_CODE], [EC5.ENTITY_NAME]), filter(nil), access([EC5.ENTITY_CODE], [EC5.ENTITY_NAME]), partitions(p0) 8 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [O1.NAME], [O2.NAME], [E.NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [EC4.ENTITY_NAME], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE], [VIEW2.M.CHANNELCODE], [VIEW2.M.USETYPE]), filter(nil), equal_conds([EC4.ENTITY_CODE = VIEW2.M.THE4S_CODE]), other_conds(nil) 9 - output([EC4.ENTITY_CODE], [EC4.ENTITY_NAME]), filter(nil), access([EC4.ENTITY_CODE], [EC4.ENTITY_NAME]), partitions(p0) 10 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [O1.NAME], [O2.NAME], [E.NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE], [VIEW2.M.CHANNELCODE], [VIEW2.M.USETYPE]), filter(nil), equal_conds([O2_GRANDPARENT.CODE = VIEW2.M.BRANCH_CODE], [O2_GRANDPARENT.ORG_ID = O2_PARENT.PARENT_ID]), other_conds(nil) 11 - output([O2_GRANDPARENT.CODE], [O2_GRANDPARENT.ORG_ID]), filter(nil), access([O2_GRANDPARENT.CODE], [O2_GRANDPARENT.ORG_ID]), partitions(p0) 12 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [O1.NAME], [O2.NAME], [E.NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE], [VIEW2.M.CHANNELCODE], [VIEW2.M.USETYPE], [VIEW2.M.BRANCH_CODE], [O2_PARENT.PARENT_ID]), filter(nil), equal_conds([O1_PARENT.CODE = VIEW2.M.BRANCH_CODE], [O1_PARENT.ORG_ID = O1.PARENT_ID]), other_conds(nil) 13 - output([O1_PARENT.CODE], [O1_PARENT.ORG_ID]), filter(nil), access([O1_PARENT.CODE], [O1_PARENT.ORG_ID]), partitions(p0) 14 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [O1.NAME], [O2.NAME], [E.NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE], [VIEW2.M.CHANNELCODE], [VIEW2.M.USETYPE], [VIEW2.M.BRANCH_CODE], [O2_PARENT.PARENT_ID], [O1.PARENT_ID]), filter(nil), equal_conds([O2_PARENT.CODE = VIEW2.M.DEPTGRP_CODE], [O2_PARENT.ORG_ID = O2.PARENT_ID]), other_conds(nil) 15 - output([O2_PARENT.CODE], [O2_PARENT.ORG_ID], [O2_PARENT.PARENT_ID]), filter(nil), access([O2_PARENT.CODE], [O2_PARENT.ORG_ID], [O2_PARENT.PARENT_ID]), partitions(p0) 16 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [O1.NAME], [O2.NAME], [E.NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE], [VIEW2.M.CHANNELCODE], [VIEW2.M.USETYPE], [VIEW2.M.BRANCH_CODE], [O1.PARENT_ID], [VIEW2.M.DEPTGRP_CODE], [O2.PARENT_ID]), filter(nil), equal_conds([O2.CODE = VIEW2.M.DEPT_CODE]), other_conds(nil) 17 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [O1.NAME], [E.NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE], [VIEW2.M.CHANNELCODE], [VIEW2.M.USETYPE], [VIEW2.M.BRANCH_CODE], [O1.PARENT_ID], [VIEW2.M.DEPTGRP_CODE], [VIEW2.M.DEPT_CODE]), filter(nil), equal_conds([O1.CODE = VIEW2.M.DEPTGRP_CODE]), other_conds(nil) 18 - output([VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [E.NAME], [VIEW2.M.DATASOURCE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.INSURE_WAY], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.THE4S_CODE], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP], [VIEW2.M.LINE], [VIEW2.M.CHANNELCODE], [VIEW2.M.USETYPE], [VIEW2.M.BRANCH_CODE], [VIEW2.M.DEPTGRP_CODE], [VIEW2.M.DEPT_CODE]), filter(nil), conds(nil), nl_params_([VIEW2.M.HANDLER_CODE], [VIEW2.M.BRANCH_CODE]) 19 - output([VIEW2.M.LINE], [VIEW2.M.DEPTGRP_CODE], [VIEW2.M.BRANCH_CODE], [VIEW2.M.DEPT_CODE], [VIEW2.M.HANDLER_CODE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.CHANNELCODE], [VIEW2.M.THE4S_CODE], [VIEW2.M.USETYPE], [VIEW2.M.INSURE_WAY], [VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [VIEW2.M.DATASOURCE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP]), filter(nil), access([VIEW2.M.LINE], [VIEW2.M.DEPTGRP_CODE], [VIEW2.M.BRANCH_CODE], [VIEW2.M.DEPT_CODE], [VIEW2.M.HANDLER_CODE], [VIEW2.M.PRODUCTGRP_CODE], [VIEW2.M.CHANNELCODE], [VIEW2.M.THE4S_CODE], [VIEW2.M.USETYPE], [VIEW2.M.INSURE_WAY], [VIEW2.M.POLICY_CHARGE_ID], [VIEW2.M.POLICY_NO], [VIEW2.M.ENDORSE_NO], [VIEW2.M.DATASOURCE], [VIEW2.M.MID_CHANNELCODE], [VIEW2.M.AGENT_CODE], [VIEW2.M.AGENT_NAME], [VIEW2.M.INSURED_NAME], [VIEW2.M.APPLICANT_NAME], [VIEW2.M.START_DATE], [VIEW2.M.END_DATE], [VIEW2.M.ISSUE_DATE], [VIEW2.M.SUBPREMIUM], [VIEW2.M.LICENSENO], [VIEW2.M.VEHICLECLAIMTYPE], [VIEW2.M.ADDVALUETAX], [VIEW2.M.AMOUNT_TYPE_I], [VIEW2.M.AMOUNT_TYPE_II], [VIEW2.M.AMOUNT_TYPE_III], [VIEW2.M.AMOUNT_TYPE_IV], [VIEW2.M.AMOUNT_TYPE_V], [VIEW2.M.AMOUNT_OVERALL], [VIEW2.M.AMOUNT_OVERALL_TOTAL], [VIEW2.M.AMOUNT_OVERALL_BRANCH], [VIEW2.M.AMOUNT_OVERALL_DEPTGRP]) 20 - output([M.LINE], [M.DEPTGRP_CODE], [M.BRANCH_CODE], [M.DEPT_CODE], [M.HANDLER_CODE], [M.PRODUCTGRP_CODE], [M.CHANNELCODE], [M.THE4S_CODE], [M.USETYPE], [M.INSURE_WAY], [M.POLICY_CHARGE_ID], [M.POLICY_NO], [M.ENDORSE_NO], [M.DATASOURCE], [M.MID_CHANNELCODE], [M.AGENT_CODE], [M.AGENT_NAME], [M.INSURED_NAME], [M.APPLICANT_NAME], [M.START_DATE], [M.END_DATE], [M.ISSUE_DATE], [M.SUBPREMIUM], [M.LICENSENO], [M.VEHICLECLAIMTYPE], [M.ADDVALUETAX], [M.AMOUNT_TYPE_I], [M.AMOUNT_TYPE_II], [M.AMOUNT_TYPE_III], [M.AMOUNT_TYPE_IV], [M.AMOUNT_TYPE_V], [M.AMOUNT_OVERALL], [M.AMOUNT_OVERALL_TOTAL], [M.AMOUNT_OVERALL_BRANCH], [M.AMOUNT_OVERALL_DEPTGRP]), filter([M.BRANCH_CODE = ?], [M.LINE = ?], [M.CHANNELCODE IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)], [M.INSURE_WAY IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]), access([M.LINE], [M.DEPTGRP_CODE], [M.BRANCH_CODE], [M.DEPT_CODE], [M.HANDLER_CODE], [M.PRODUCTGRP_CODE], [M.CHANNELCODE], [M.THE4S_CODE], [M.USETYPE], [M.INSURE_WAY], [M.POLICY_CHARGE_ID], [M.POLICY_NO], [M.ENDORSE_NO], [M.DATASOURCE], [M.MID_CHANNELCODE], [M.AGENT_CODE], [M.AGENT_NAME], [M.INSURED_NAME], [M.APPLICANT_NAME], [M.START_DATE], [M.END_DATE], [M.ISSUE_DATE], [M.SUBPREMIUM], [M.LICENSENO], [M.VEHICLECLAIMTYPE], [M.ADDVALUETAX], [M.AMOUNT_TYPE_I], [M.AMOUNT_TYPE_II], [M.AMOUNT_TYPE_III], [M.AMOUNT_TYPE_IV], [M.AMOUNT_TYPE_V], [M.AMOUNT_OVERALL], [M.AMOUNT_OVERALL_TOTAL], [M.AMOUNT_OVERALL_BRANCH], [M.AMOUNT_OVERALL_DEPTGRP]), partitions(p0), limit(?), offset(nil) 21 - output([E.NAME]), filter(nil), access([E.NAME]), partitions(p0) 22 - output([O1.CODE], [O1.PARENT_ID], [O1.NAME]), filter(nil), access([O1.CODE], [O1.PARENT_ID], [O1.NAME]), partitions(p0) 23 - output([O2.CODE], [O2.PARENT_ID], [O2.NAME]), filter(nil), access([O2.CODE], [O2.PARENT_ID], [O2.NAME]), partitions(p0) 24 - output([EC.ENTITY_CODE], [EC.ENTITY_NAME]), filter([EC.ENTITY_CODE = ?]), access([EC.ENTITY_CODE], [EC.ENTITY_NAME]), partitions(p0)
可以看到改写完后，原来执行计划 SUBPLAN FILTER 已经变成了 HASH RIGHT OUTER JOIN，而执行时间也从原来的 3184s 变成了 10.91s，直接起飞。
总结：近两年感觉国产数据库市场份额越来越高了，笔者SQL优化的案例更多得也是从ORACLE、MySQL变成了OB、金仓数据库，
建议大家还是要好好深入学习下国产数据库（特别是金仓数据库，国产之光），免得35岁被优化了找不到工作 。 