rownum优化 sqlserver_Oracle rownum 分页引起的效率问题及优化思路

rownum<=90 执行计划和时间

SQL> set autot traceonly

SQL> select *

2    from (SELECT EMP.UNIQ_NO,

3                 EMP.EMP_NAME,

4                 EMP.SEX,

5                 CER.CARD_TYPE,

6                 CER.CARD_NUM,

7                 EMP.IS_BEIJING ENTRYTYPE,

8                 EMP.EMP_TYPE,

9                 EMP.HOUSEHOLD_ADDR

10            FROM ES_EMP_INFO EMP, ES_EMP_CER CER

11           WHERE 1 = 1

12             AND EMP.UNIQ_NO = CER.UNIQ_NO(+)

13             AND CER.IS_APP_UNIQ_NO(+) = 1

14             AND CER.IS_VALID(+) = 1

15             AND EXISTS

16           (SELECT 1

17                    FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD

18                   WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID

19                     AND LATION.IS_VALID = 1

20                     AND HOLD.IS_VALID = 1

21                     AND HOLD.DOOR_STATE = 1

22                     AND LATION.UNIQ_NO = EMP.UNIQ_NO)

23             AND NOT EXISTS

24           (SELECT 1 FROM CS_ORDER CO WHERE CO.UNIQ_NO = EMP.UNIQ_NO)

25           ORDER BY EMP.UNIQ_NO)

26where rownum <=90;

90 rows selected.

Elapsed: 00:10:56.77

Execution Plan

----------------------------------------------------------

Plan hash value: 1623837912

---------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time   |

---------------------------------------------------------------------------------------------------------

|   0 | SELE