oracle 11g:
一共有大约90万的数据.
sql如下:
————————————————————————-
SELECT *
FROM (SELECT T.ID,
T.MSTID,
T.SHOUDFID,
T.SHOUDFMC,
T.SONGDFID,
T.SONGDFMC,
T.YSCP,
T.WLH,
T.WLMS,
T.CJH,
T.FDJH,
T.BZ,
T.SCSJ,
T.CCSJ,
T.BL1,
T.BL2,
T.BL3,
T.DQZT,
T.KWH,
T.RKSJ,
T.CJRID,
T.RKRID,
T.FHDH,
T.SFSC,
T.CPID,
T.DQUNITID,
T5.ID LSID,
T5.LSSJ,
T5.DARSJ,
T5.UNITID LSDWBM,
T5.UNITNAME LSDWMC,
T5.DARDWBM,
T5.DARDWMC,
T5.CJSJ LSCJSJ,
T5.CJDWBM,
T5.CJDWMC,
T5.DAKJSSJ,
T5.DJQ1JSSJ,
T5.DJQ2JSSJ,
T5.DDZT LSDQZT,
T4.XM,
T4.XB,
T4.SFZH,
T4.CSRQ,
T4.LXDH,
T4.YB,
T4.DZ,
T4.BL1 LXDZDH,
T2.UNITTYPE,
T2.UJC,
T.SJC,
T.CJSJ,
ROW_NUMBER() OVER(ORDER BY T.DQUNITID, T.ID) RN
FROM T_D_ZTRK_SLV T
LEFT JOIN T_B_UNIT T2 ON T.DQUNITID = T2.UNITID
LEFT JOIN T_D_RETAIL_CLXX_EXP T3 ON T.ID = T3.RKID
LEFT JOIN T_D_RETAIL T5 ON (T3.DDID = T5.ID AND T5.DDZT <> 2)
LEFT JOIN T_D_RETAIL_YHXX_EXP T4 ON (T4.DDID = T5.ID AND
T5.DDZT <> 2)
WHERE ((T.DQZT IN (2, 3, 4, 5, 6, 8) OR
(T.DQZT = 1 AND KWH IN (‘00002’))) OR
(T.DQZT IN (2, 3, 4, 5, 6, 8, 7) AND
(T.SCSJ >= ‘19900101’ AND T.SCSJ <= ‘20151224’))))
WHERE RN >= 1
AND RN < 5001
————————————————————————–
通过plsql执行
发现1-5001时用时7秒
5000001-505001时用时30秒
8000001-805001时用时93秒
row_number分页 是越到后面越慢吗?有没有什么好的分页解决方法。最好举例说明