Oracle数据库排序后分页慢的问题

2018-02-03 10:22:41来源:oschina作者:chencang001人点击

分享

传统思路:


SELECT *
FROM (SELECT TMP_PAGE.*, ROWNUM ROW_ID
FROM (SELECT LG.MESSAGE_ID messageId,
LG.PRODUCTION_ID productionId,
NVL (LG.PRODUCTION_NAME, '汇总控制')
productionName,
LG.RULE_ID ruleId,
LG.RULE_NAME ruleName,
LG.MESSAGE_CONTENT messageContent,
SUBSTR (LG.MESSAGE_CONTENT,
0,
INSTR (LG.MESSAGE_CONTENT,
'br/>',
1,
1)
- 2)
messageHead,
DECODE (LG.STEP,
'0', '事前风控',
'1', '事中监控',
'事后巡检')
step,
LG.COMUPTE_DATE computeDate,
TO_CHAR (SG.SEND_TIME, 'yyyy/MM/dd HH24:mi:ss')
sendDate
FROM RC_MESSAGE_LOG LG
INNER JOIN RC_SEND_MESSAGE_LOG SG
ON LG.MESSAGE_SEND_LOG = SG.MESSAGE_SEND_LOG
WHERE 1 = 1
AND LG.STEP IN ('1', '2')
AND LG.COMUPTE_DATE >= 20171225
AND LG.COMUPTE_DATE <= 20180201
-- AND LG.STEP IN (?, ?)
ORDER BY messageId DESC) TMP_PAGE
WHERE ROWNUM <= 31570)
WHERE ROW_ID > 31560

换一种思路:先根据条件取出rowId,然后根据rowId关联出对应的记录


SELECT LG.MESSAGE_ID messageId,
LG.PRODUCTION_ID productionId,
NVL (LG.PRODUCTION_NAME, '汇总控制')
productionName,
LG.RULE_ID ruleId,
LG.RULE_NAME ruleName,
LG.MESSAGE_CONTENT messageContent,
SUBSTR (LG.MESSAGE_CONTENT,
0,
INSTR (LG.MESSAGE_CONTENT,
'br/>',
1,
1)
- 2)
messageHead,
DECODE (LG.STEP,
'0', '事前风控',
'1', '事中监控',
'事后巡检')
step,
LG.COMUPTE_DATE computeDate,
TO_CHAR (SG.SEND_TIME, 'yyyy/MM/dd HH24:mi:ss')
sendDate
FROM RC_MESSAGE_LOG LG
INNER JOIN RC_SEND_MESSAGE_LOG SG
ON LG.MESSAGE_SEND_LOG = SG.MESSAGE_SEND_LOG,
(SELECT rid
FROM (SELECT ROWNUM rn, t.rid
FROM (SELECT LG1.ROWID rid
FROM RC_MESSAGE_LOG LG1
INNER JOIN RC_SEND_MESSAGE_LOG SG
ON LG1.MESSAGE_SEND_LOG = SG.MESSAGE_SEND_LOG
where LG1.STEP IN ('1', '2')
AND LG1.COMUPTE_DATE >= 20171225
AND LG1.COMUPTE_DATE <= 20180201
ORDER BY LG1.MESSAGE_ID DESC) t
WHERE ROWNUM <= 31570)
WHERE rn > 31560) t2
WHERE LG.ROWID = t2.rid
and 1 = 1
order by LG.MESSAGE_ID desc

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台