某金融保险公司业务中这条sql在oracle中跑非常快，而在OB上跑需要25s，找我们这边看看是否还有优化空间，本人不才，懂一点点SQL优化的皮毛，做个简单的记录。
--原慢sql如下：35s
select count(1) from
( select count(*) LISTNUM from sx.t_AAA a
LEFT JOIN (select STAFF_CODE, policy_id from sx.T_spop where flag = '3' ) pn on pn.policy_id = a.policy_id
LEFT JOIN (
SELECT to_char(pay2.INTO_ACCOUNT_DATE, 'yyyy-mm-dd hh:mm:ss') accountDate, pay1.policy_id
FROM sx.t_spopa pay1,
sx.t_spa pay2
WHERE pay2.PAY_APPLY_ID = pay1.PAY_APPLY_ID
AND pay1.INSURER_SUB_COMPANY_CODE IN('3070100')
AND pay2.sub_company IN('3070100')
AND pay1.PAY_STATUS IN ('1', '2', '3', '4') ) pa ON a.POLICY_ID = pa.POLICY_ID
INNER JOIN ( select policy_id policy_id_D from sx.t_sufd group by policy_id ) b on a.policy_id = b.policy_id_D
WHERE 1 = 1
AND a.POLICY_NO IS NOT NULL
AND SUBSTR(VERSION, INSTR(VERSION, '.', 1), 2) = '.0'
AND pa.accountDate >= to_date('2024-11-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND pa.accountDate <= to_date('2024-11-22 23:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND A.INSURER_SUB_COMPANY_CODE in ('3070100')
AND A.OWB_SUP_BRANCH_CODE in ( select F_ORGAN_ID from om w where w.if_display = '0' and w.F_CODE_P09 in ('350500'))
AND A.CHANNEL in ('11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '23', '51', '52', '71')
)
--执行计划：
| ID | OPERATOR | NAME | EST. ROWS | COST |
|---|---|---|---|---|
| 0 | SCALAR GROUP BY | 1 | 141898 | |
| 1 | SUBPLAN SCAN | 1 | 141898 | |
| 2 | SCALAR GROUP BY | 1 | 141898 | |
| 3 | NESTED-LOOP JOIN | 8 | 141838 | |
| 4 | NESTED-LOOP OUTER JOIN | 6 | 141637 | |
| 5 | NESTED-LOOP JOIN | 5 | 141242 | |
| 6 | NESTED-LOOP JOIN | 175 | 75421 | |
| 7 | NESTED-LOOP JOIN | 161 | 12746 | |
| 8 | TABLE SCAN | W(IDX_ORGAN_02) | 1 | 92 |
| 9 | TABLE SCAN | a(IDX_TEST_1126_2) | 321 | 25235 |
| 10 | TABLE LOOKUP | PAY1 | 17 | 387 |
| 11 | DISTRIBUTED TABLE SCAN | PAY1(IDX_POLICY_PAY_APPLY_01) | 17 | 9 |
| 12 | TABLE LOOKUP | PAY2 | 2 | 376 |
| 13 | DISTRIBUTED TABLE SCAN | PAY2(IDX_PAY_APPLY_02) | 18 | 10 |
| 14 | TABLE SCAN | T_spop(idx_pol_01) | 1 | 83 |
| 15 | SUBPLAN SCAN | B | 1 | 46 |
| 16 | MERGE GROUP BY | 1 | 46 | |
| 17 | TABLE SCAN | t_sufd(idx_sufd_01) | 2 | 46 |
执行计划后续的没有特别有用的信息，于是没有全部粘贴列出。
--分析：
看了执行计划对比后，目前感觉OB没有走到理想的状态，和oracle的CBO执行还是有比较大的区别，很抱歉oracle生产库没法拿出执行计划做对比。
检查了left join的耗时状况：
select STAFF_CODE, policy_id from sx.T_spop where flag = '3'；
--返回2.2w行，耗时9s
SELECT to_char(pay2.INTO_ACCOUNT_DATE, 'yyyy-mm-dd hh:mm:ss') accountDate, pay1.policy_id
FROM sx.t_spopa pay1,
sx.t_spa pay2
WHERE pay2.PAY_APPLY_ID = pay1.PAY_APPLY_ID
AND pay1.INSURER_SUB_COMPANY_CODE IN('3070100')
AND pay2.sub_company IN('3070100')
AND pay1.PAY_STATUS IN ('1', '2', '3', '4')；
--返回113w行，耗时3.84s
select policy_id policy_id_D from sx.t_sufd group by policy_id；
--返回1545w行，耗时21s
并结合关联列的情况，这样看确实能看到大致能看出哪里出了问题，于是改写了下：
select count(*) LISTNUM from sx.t_AAA a
where A.CHANNEL in ('11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '23', '51', '52', '71')
and A.INSURER_SUB_COMPANY_CODE in ('3070100')
and SUBSTR(VERSION, INSTR(VERSION, '.', 1), 2) = '.0'
and a.POLICY_NO IS NOT NULL
and EXISTS(SELECT 1 from om w where w.if_display = '0' and w.F_CODE_P09 in ('350500') and A.OWB_SUP_BRANCH_CODE = w.F_ORGAN_ID)
and EXISTS(SELECT 1 FROM sx.t_sufd b where a.policy_id = b.policy_id)
and EXISTS(SELECT 1 FROM sx.t_spopa pay1,
sx.t_spa pay2
WHERE pay2.PAY_APPLY_ID = pay1.PAY_APPLY_ID
AND pay1.INSURER_SUB_COMPANY_CODE IN('3070100')
AND pay2.sub_company IN('3070100')
AND pay1.PAY_STATUS IN ('1', '2', '3', '4')
AND a.POLICY_ID = pay1.POLICY_ID
AND pay2.INTO_ACCOUNT_DATE >= to_date('2024-11-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND pay2.INTO_ACCOUNT_DATE <= to_date('2024-11-22 23:00:00', 'yyyy-mm-dd hh24:mi:ss'))
and EXISTS(SELECT 1 FROM sx.T_spop where flag = '3' AND policy_id = a.policy_id);
执行计划：
| ID | OPERATOR | NAME | EST. ROWS | COST |
|---|---|---|---|---|
| 0 | SCALAR GROUP BY | 1 | 459 | |
| 1 | NESTED-LOOP SEMI JOIN | 0 | 459 | |
| 2 | NESTED-LOOP SEMI JOIN | 0 | 459 | |
| 3 | NESTED-LOOP JOIN | 0 | 459 | |
| 4 | NESTED-LOOP JOIN | 0 | 459 | |
| 5 | SUPLAN SCAN | VIEW6 | 0 | 459 |
| 6 | MERGE DISTINCT | 0 | 459 | |
| 7 | SORT | 0 | 459 | |
| 8 | NESTED-LOOP JOIN | 0 | 459 | |
| 9 | TABLE LOOKUP | PAY2 | 1 | 92 |
| 10 | DISTRIBUTED TABLE SCAN | PAY2(IDX_PAY_APPLY_06) | 1 | 46 |
| 11 | TABLE LOOKUP | PAY1 | 16 | 364 |
| 12 | DISTRIBUTED TABLE SCAN | PAY1(IDX_POLICY_PAY_APPLY_11) | 16 | 9 |
| 13 | TABLE LOOKUP | A | 1 | 29 |
| 14 | DISTRIBUTED LOOKUP | A(PK_t_AAA) | 1 | 5 |
| 15 | TABLE GET | W | 1 | 31 |
| 16 | TABLE SCAN | B(IDX_UN_FEE_DETAIL_01) | 1 | 21 |
| 17 | SUPPLAN SCAN | VIEW4 | 1 | 229 |
| 18 | TABLE SCAN | T_spop(idx_pol_01) | 1 | 228 |
--改写后SQL在OB上跑了336us。
总结：
原慢sql是视图合并了引起执行计划乱且引起谓词推入不了，不能提前过滤数据，于是改为手动推入提前过滤数据后，性能得到了非常大的提升。
另一方面看Oceanbase的CBO虽然没有oracle那么强，在信创的道路上Oceanbase各方面的表现还是很不错了。