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;
标签：STATUS,task,pp,DM,id,案例,jbt,SQL,ID From： https://www.cnblogs.com/yuzhijian/p/17023379.html