加快排序與分頁

在狼行天下的 blog中發現以下內容,可以排序與分頁row_number() over(order by create_time desc) rk

[@more@]

在開發中應用

慢

select B.*
from (select A.*, ROWNUM ROWNO
from (select A.TITLE,
TO_CHAR(A.CREATE_TIME, 'YYYY.MM.DD') AS CREATE_TIME,
A.AUTHOR_ID,
A.DOC_ID,
A.ABSTRACT,
A.PIC_ID AS IMAGE_ID,
B.TYPE_ID
from CT_DOCUMENT_L A,
(SELECT DOC_ID, TYPE_ID, ROWNUM AS ROWNO
FROM RL_CT_DOC_ITEM
WHERE ITEM_ID = 4581) B
WHERE A.DOC_ID = B.DOC_ID
and A.STATUS = 10
) A) B
where B.ROWNO between 1 and 20

快
select * from (
select bb.TITLE,TO_CHAR(bb.CREATE_TIME, 'YYYY.MM.DD') AS CREATE_TIME,
bb.AUTHOR_ID,aa.DOC_ID,bb.ABSTRACT,bb.PIC_ID AS IMAGE_ID,bb.TYPE_ID
, row_number() over(order by bb.type_id,bb.create_time desc) rk
from rl_ct_doc_item aa,CT_DOCUMENT_L bb
where aa.DOC_ID=bb.DOC_ID
and aa.ITEM_ID= 4581
and bb.STATUS= 10
) B
where B.rk between 1 and 20