oracle解决排序慢,Oracle数据库排序后分页慢的问题

传统思路：

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