DM数据库SQL分页案例
DM一哥们找我优化条分页的SQL语句，结果集很小返回99行数据，废话不说安排一下。
原始SQL语句如下，保密要求，给真实的表名换了别名：
SELECT count(*) FROM (SELECT TMP.*, ROWNUM ROW_ID FROM (select * from (select pp.BIZ_PERSON_ID partyPersonOid, pp.BIZ_PERSON_ID bizPersonId, pp.PERSON_ID personOid, pp.PERSON_ID personId, pp.A01065 a01065, jbt.task_oid taskOid, pp.A01084 idNo, pp.A01001 name, jfa34.jfa34009 jfa34009, jfa34.jfa34010 jfa34010, jfa34.jfa34013 jfa34013, jfa34.jfa34014 jfa34014, bu.UNIT_ID unitOid, bu.B01001 unitName, bu.JFB01002 adminUnitName, bu.PARENT_UNIT_ID parentUnitId, (CASE WHEN jfw01.JFW01009 = '1' THEN '是' WHEN jfw01.JFW01009 = '0' THEN '否' else '' end) isTfPerson, jbt.CREATED_DATE createdDate, jbt.UPDATED_DATE updatedDate, jbt.PROCESS_RESULT processResult, jbt.start_time startTime, jbt.complete_time completeTime, jbt.CREATED_DATE creatTaskTime, jbt.UPDATED_DATE updateTaskTime, jbt.item_code itemCode, jbt.BIZ_STATUS_CODE bizStatusCode, jbt.BIZ_STATUS_NAME bizStatusName, jbt.PRE_BIZ_STATUS_CODE preBizStatusCode, jbt.PRE_BIZ_STATUS_NAME preBizStatusName, jbt.AUDIT_STATUS_CODE auditStatusCode, jbt.AUDIT_STATUS_NAME auditStatusName, jbt.PRE_AUDIT_STATUS_CODE preAuditStatusCode, jbt.PRE_AUDIT_STATUS_NAME preAuditStatusName, jbt.PROCESS_DEPT_CODE processDeptCode, jbt.PROCESS_DEPT_NAME processDeptName, jbti.task_item_code taskItemCode, jbti.task_item_name taskItemName, jbti.pre_task_item_code preTaskItemCode, jbti.pre_task_item_name preTaskItemName, jfa34002 dutyPost, jfa34005 dutyPosition, row_number() over (partition by jbt.task_oid order by jbti.UPDATED_DATE desc) as rn, (case when jbt.BIZ_STATUS_CODE = 'WU999' then '' else (SELECT bl.REMARK FROM qqqqq bl where bl.biz_Status_Code IN ('WU104', 'WU107', 'WM106', 'WM109', 'WM112', 'WU110', 'WM115', 'WU110', 'WM115') AND bl.TASK_OID = jbt.TASK_OID order by bl.CREATED_DATE DESC LIMIT 1) end) reCallOpinion, to_char(rr.a15021, 'yyyy') as promoteYear, rr.a15017 as reviewResultType FROM aaaaa jbti, bbbbb jbt inner join ccccc pp on jbt.task_oid = pp.TASK_ID left join ddddd jfw01 on pp.biz_person_id = jfw01.biz_person_id left join uuuuu jfa34 on pp.biz_person_id = jfa34.biz_person_id left join vvvvv bu on pp.UNIT_ID = bu.UNIT_ID left join (select ROW_NUMBER() OVER (PARTITION BY biz_person_id ORDER BY a15021 DESC, handle_Mark desc, id desc) AS num, a15021, a15017, biz_person_id from rrrrr) rr on pp.biz_person_id = rr.biz_person_id and rr.num = '1' WHERE jbt.task_oid = jbti.task_oid and pp.UNIT_ID in (select unit_oid from sssss where user_id = 'admin') AND jbti.TASK_ITEM_STATUS = '1' AND jbti.TASK_ITEM_CODE in (select jmn.FLOW_NODE_CODE from jjjjj jmn where jmn.MENU_CODE = 'B742101101') ORDER BY jbt.UPDATED_DATE DESC) WHERE rn = 1) TMP WHERE ROWNUM <= 100) WHERE ROW_ID > 1;
执行时间：
DM数据库的执行计划太难看了，直接忽略，用瞪眼大法观察下SQL大致看看是哪里慢的。
这段标量子查询去掉以后，单独把SQL拿出来跑，不加外层分页代码，0.1S能出结果，137条记录，大致判断是这里导致慢的因素。
select pp.BIZ_PERSON_ID partyPersonOid, pp.BIZ_PERSON_ID bizPersonId, pp.PERSON_ID personOid, pp.PERSON_ID personId, pp.A01065 a01065, jbt.task_oid taskOid, pp.A01084 idNo, pp.A01001 name, jfa34.jfa34009 jfa34009, jfa34.jfa34010 jfa34010, jfa34.jfa34013 jfa34013, jfa34.jfa34014 jfa34014, bu.UNIT_ID unitOid, bu.B01001 unitName, bu.JFB01002 adminUnitName, bu.PARENT_UNIT_ID parentUnitId, (CASE WHEN jfw01.JFW01009 = '1' THEN '是' WHEN jfw01.JFW01009 = '0' THEN '否' else '' end) isTfPerson, jbt.CREATED_DATE createdDate, jbt.UPDATED_DATE updatedDate, jbt.PROCESS_RESULT processResult, jbt.start_time startTime, jbt.complete_time completeTime, jbt.CREATED_DATE creatTaskTime, jbt.UPDATED_DATE updateTaskTime, jbt.item_code itemCode, jbt.BIZ_STATUS_CODE bizStatusCode, jbt.BIZ_STATUS_NAME bizStatusName, jbt.PRE_BIZ_STATUS_CODE preBizStatusCode, jbt.PRE_BIZ_STATUS_NAME preBizStatusName, jbt.AUDIT_STATUS_CODE auditStatusCode, jbt.AUDIT_STATUS_NAME auditStatusName, jbt.PRE_AUDIT_STATUS_CODE preAuditStatusCode, jbt.PRE_AUDIT_STATUS_NAME preAuditStatusName, jbt.PROCESS_DEPT_CODE processDeptCode, jbt.PROCESS_DEPT_NAME processDeptName, jbti.task_item_code taskItemCode, jbti.task_item_name taskItemName, jbti.pre_task_item_code preTaskItemCode, jbti.pre_task_item_name preTaskItemName, jfa34002 dutyPost, jfa34005 dutyPosition, row_number() over (partition by jbt.task_oid order by jbti.UPDATED_DATE desc) as rn, to_char(rr.a15021, 'yyyy') as promoteYear, rr.a15017 as reviewResultType FROM aaaaa jbti, bbbbb jbt inner join ccccc pp on jbt.task_oid = pp.TASK_ID left join ddddd jfw01 on pp.biz_person_id = jfw01.biz_person_id left join uuuuu jfa34 on pp.biz_person_id = jfa34.biz_person_id left join vvvvv bu on pp.UNIT_ID = bu.UNIT_ID left join (select ROW_NUMBER() OVER (PARTITION BY biz_person_id ORDER BY a15021 DESC, handle_Mark desc, id desc) AS num, a15021, a15017, biz_person_id from rrrrr) rr on pp.biz_person_id = rr.biz_person_id and rr.num = '1' WHERE jbt.task_oid = jbti.task_oid and pp.UNIT_ID in (select unit_oid from sssss where user_id = 'admin') AND jbti.TASK_ITEM_STATUS = '1' AND jbti.TASK_ITEM_CODE in (select jmn.FLOW_NODE_CODE from jjjjj jmn where jmn.MENU_CODE = 'B742101101') ORDER BY jbt.UPDATED_DATE DESC
qqqqq 表加个联合索引再跑分页语句试试。
CREATE INDEX "IDX_TASK_BIZ" ON "qqqqq"("TASK_OID" ASC,"BIZ_STATUS_CODE" ASC) STORAGE(ON "hzgz_xcuatdb", CLUSTERBTR);
还是需要2.34S才能出结果，这个时候笔者就在想会不会是分页框架提供的分页方式不对，换个分页写法再试试。
-- 使用新的分页模板，没改语句 SELECT count(*) FROM (SELECT * FROM (SELECT t.*, rownum ROW_ID FROM (select pp.BIZ_PERSON_ID partyPersonOid, pp.BIZ_PERSON_ID bizPersonId, pp.PERSON_ID personOid, pp.PERSON_ID personId, pp.A01065 a01065, jbt.task_oid taskOid, pp.A01084 idNo, pp.A01001 name, jfa34.jfa34009 jfa34009, jfa34.jfa34010 jfa34010, jfa34.jfa34013 jfa34013, jfa34.jfa34014 jfa34014, bu.UNIT_ID unitOid, bu.B01001 unitName, bu.JFB01002 adminUnitName, bu.PARENT_UNIT_ID parentUnitId, (CASE WHEN jfw01.JFW01009 = '1' THEN '是' WHEN jfw01.JFW01009 = '0' THEN '否' else '' end) isTfPerson, jbt.CREATED_DATE createdDate, jbt.UPDATED_DATE updatedDate, jbt.PROCESS_RESULT processResult, jbt.start_time startTime, jbt.complete_time completeTime, jbt.CREATED_DATE creatTaskTime, jbt.UPDATED_DATE updateTaskTime, jbt.item_code itemCode, jbt.BIZ_STATUS_CODE bizStatusCode, jbt.BIZ_STATUS_NAME bizStatusName, jbt.PRE_BIZ_STATUS_CODE preBizStatusCode, jbt.PRE_BIZ_STATUS_NAME preBizStatusName, jbt.AUDIT_STATUS_CODE auditStatusCode, jbt.AUDIT_STATUS_NAME auditStatusName, jbt.PRE_AUDIT_STATUS_CODE preAuditStatusCode, jbt.PRE_AUDIT_STATUS_NAME preAuditStatusName, jbt.PROCESS_DEPT_CODE processDeptCode, jbt.PROCESS_DEPT_NAME processDeptName, jbti.task_item_code taskItemCode, jbti.task_item_name taskItemName, jbti.pre_task_item_code preTaskItemCode, jbti.pre_task_item_name preTaskItemName, jfa34002 dutyPost, jfa34005 dutyPosition, row_number() over (partition by jbt.task_oid order by jbti.UPDATED_DATE desc) as rn, (case when jbt.BIZ_STATUS_CODE = 'WU999' then '' else (SELECT bl.REMARK FROM qqqqq bl where bl.biz_Status_Code IN ('WU104', 'WU107', 'WM106', 'WM109', 'WM112', 'WU110', 'WM115', 'WU110', 'WM115') AND bl.TASK_OID = jbt.TASK_OID order by bl.CREATED_DATE DESC LIMIT 1) end) reCallOpinion, to_char(rr.a15021, 'yyyy') as promoteYear, rr.a15017 as reviewResultType FROM aaaaa jbti, bbbbb jbt inner join ccccc pp on jbt.task_oid = pp.TASK_ID left join ddddd jfw01 on pp.biz_person_id = jfw01.biz_person_id left join uuuuu jfa34 on pp.biz_person_id = jfa34.biz_person_id left join vvvvv bu on pp.UNIT_ID = bu.UNIT_ID left join (select ROW_NUMBER() OVER (PARTITION BY biz_person_id ORDER BY a15021 DESC, handle_Mark desc, id desc) AS num, a15021, a15017, biz_person_id from rrrrr) rr on pp.biz_person_id = rr.biz_person_id and rr.num = '1' WHERE jbt.task_oid = jbti.task_oid and pp.UNIT_ID in (select unit_oid from sssss where user_id = 'admin') AND jbti.TASK_ITEM_STATUS = '1' AND jbti.TASK_ITEM_CODE in (select jmn.FLOW_NODE_CODE from jjjjj jmn where jmn.MENU_CODE = 'B742101101') ORDER BY jbt.UPDATED_DATE DESC) t) WHERE rownum <= 100) WHERE ROW_ID >= 1;
可以看到新的分页语句0.085S就能出结果了，简直秒杀。
总结：开发框架提的分页插件有可能提供错误的分页框架，会极大影响SQL语句原有的性能，需要多测试才能知道分页语句的性能是否符合性能要求，下面笔者提供个正确的分页框架：
select * from (select * from (select a.*, rownum rn from ( 需要分页的 SQL
) a) where rownum <= 10) where rn >= 1;
最后，提供个left join 等价改写的方式干掉上面的标量子查询，但是在本案例中等价改写方式并没有太大性能提升，仅供娱乐：
-- 改分页模板，改SQL SELECT count(*) FROM (SELECT * FROM (SELECT t.*, rownum ROW_ID FROM (SELECT pp.BIZ_PERSON_ID partyPersonOid, pp.BIZ_PERSON_ID bizPersonId, pp.PERSON_ID personOid, pp.PERSON_ID personId, pp.A01065 a01065, jbt.task_oid taskOid, pp.A01084 idNo, pp.A01001 NAME, jfa34.jfa34009 jfa34009, jfa34.jfa34010 jfa34010, jfa34.jfa34013 jfa34013, jfa34.jfa34014 jfa34014, bu.UNIT_ID unitOid, bu.B01001 unitName, bu.JFB01002 adminUnitName, bu.PARENT_UNIT_ID parentUnitId, (CASE WHEN jfw01.JFW01009 = '1' THEN '是' WHEN jfw01.JFW01009 = '0' THEN '否' ELSE '' END) isTfPerson, jbt.CREATED_DATE createdDate, jbt.UPDATED_DATE updatedDate, jbt.PROCESS_RESULT processResult, jbt.start_time startTime, jbt.complete_time completeTime, jbt.CREATED_DATE creatTaskTime, jbt.UPDATED_DATE updateTaskTime, jbt.item_code itemCode, jbt.BIZ_STATUS_CODE bizStatusCode, jbt.BIZ_STATUS_NAME bizStatusName, jbt.PRE_BIZ_STATUS_CODE preBizStatusCode, jbt.PRE_BIZ_STATUS_NAME preBizStatusName, jbt.AUDIT_STATUS_CODE auditStatusCode, jbt.AUDIT_STATUS_NAME auditStatusName, jbt.PRE_AUDIT_STATUS_CODE preAuditStatusCode, jbt.PRE_AUDIT_STATUS_NAME preAuditStatusName, jbt.PROCESS_DEPT_CODE processDeptCode, jbt.PROCESS_DEPT_NAME processDeptName, jbti.task_item_code taskItemCode, jbti.task_item_name taskItemName, jbti.pre_task_item_code preTaskItemCode, jbti.pre_task_item_name preTaskItemName, jfa34002 dutyPost, jfa34005 dutyPosition, row_number() over (PARTITION BY jbt.task_oid ORDER BY jbti.UPDATED_DATE DESC) AS rn, (CASE WHEN jbt.BIZ_STATUS_CODE = 'WU999' THEN '' ELSE bl.REMARK END) reCallOpinion, to_char(rr.a15021, 'yyyy') AS promoteYear, rr.a15017 AS reviewResultType FROM aaaaa jbti, bbbbb jbt left join (select * from (select REMARK, TASK_OID, row_number() over (PARTITION by TASK_OID ORDER by CREATED_DATE desc ) as rn from qqqqq where biz_Status_Code in ('WU104', 'WU107', 'WM106', 'WM109', 'WM112', 'WU110', 'WM115', 'WU110', 'WM115')) where rn = 1) bl on jbt.task_oid = bl.task_oid INNER JOIN ccccc pp ON jbt.task_oid = pp.TASK_ID LEFT JOIN ddddd jfw01 ON pp.biz_person_id = jfw01.biz_person_id LEFT JOIN uuuuu jfa34 ON pp.biz_person_id = jfa34.biz_person_id LEFT JOIN vvvvv bu ON pp.UNIT_ID = bu.UNIT_ID LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY biz_person_id ORDER BY a15021 DESC, handle_Mark DESC, id DESC) AS num, a15021, a15017, biz_person_id FROM rrrrr) rr ON pp.biz_person_id = rr.biz_person_id AND rr.num = '1' WHERE jbt.task_oid = jbti.task_oid AND pp.UNIT_ID IN (SELECT unit_oid FROM sssss WHERE user_id = 'admin') AND jbti.TASK_ITEM_STATUS = '1' AND jbti.TASK_ITEM_CODE IN (SELECT jmn.FLOW_NODE_CODE FROM jjjjj jmn WHERE jmn.MENU_CODE = 'B742101101') ORDER BY jbt.UPDATED_DATE DESC) t) WHERE rownum <= 99) WHERE ROW_ID >= 1;
