How to troubleshoot sorting and efficiency issues in Oracle paging queries

This article will combine the author's recent work, in the Oracle database paging query, encountered a small problem, for you to explain how to solve the Oracle paging query sorting and efficiency issues.

The original paging query SQL code is as follows:

Select RowNum RN, t.id ID, o.name yyb,u.name XM, T.MC from Tzdysx

T,tuser u,lborganization o where t.cjr=u.id and u.orgid=o.orgcode and T.GX = 1

ORDER BY ID

The results are as follows:

RN ID yyb XM MC

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

3 49 A Securities headquarters administrator test

4 96 head of a security headquarters holding more than 10000 shares in Hong Kong

5 102 ten-Year plan for a security headquarters manager

14 105 A security headquarters manager development channel for Shanghai

11 106 A security headquarters of the manager Vanke a

12 107 A Security headquarters Administrator 11

13 108 The client of a security HQ today for the birthday

2 109 A Security headquarters administrator customer status Normal

6 110 A Security headquarters manager without a deal

7 111 A Security headquarters administrator OA

8 112 Manager of a security headquarters lucky customer

9 113 A Security headquarters manager risk type

10 114 A Security headquarters administrator TST

22 115 White Sands Online trading An Changbio Ann Customer Normal

1 118 A Security headquarters Administrator 213

18 119 A Securities headquarters administrator customer number contains 1008

17 120 A security headquarters manager AAA

19 123 A security headquarters administrator Ssssssss

20 124 A Security headquarters Administrator www

21 126 A Security headquarters administrator 123123

15 127 A Security headquarters administrator 1212

16 128 A Security headquarters administrator aaaaaa

Rows selected

Initially I used the following SQL code query:

SELECT * FROM (select RowNum RN, t.id ID, o.name yyb,u.name XM, T.MC

From Tzdysx t,tuser u,lborganization o where t.cjr=u.id and U.orgid=o.orgcode and

T.GX = 1 ORDER by t.id Where rn>10 and rn<=20;

This method succeeds in paging, and the results are as follows:

RN ID yyb XM MC

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

14 105 A security headquarters manager development channel for Shanghai

11 106 A security headquarters of the manager Vanke a

12 107 A Security headquarters Administrator 11

13 108 The client of a security HQ today for the birthday

18 119 A Securities headquarters administrator customer number contains 1008

17 120 A security headquarters manager AAA

19 123 A security headquarters administrator Ssssssss

20 124 A Security headquarters Administrator www

15 127 A Security headquarters administrator 1212

16 128 A Security headquarters administrator aaaaaa

Ten rows selected