What is the principle of oracle paging query

Oracle's paging query principle: 1. Use the characteristics of ORACLE's rownum field to query; 2. Query the specified number of N pieces of data; 3. Remove the M pieces of data from the specified number of N pieces of query results; 4. In web development, by passing M and N as parameters, you can get paginated results.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the principle of oracle paging query?

The principle of paging:

1. Use the characteristics of the rownum field of ORACLE to query.

2. Query the first N pieces of data

3. Get M to N pieces of data from the query results in step 2

4. In web development Pass M and N as parameters to get paginated results.

Example: Query 20~30 records in the table, the sql statement is as follows.

select *   from( 
 select   rownum  as  pageNo,  A.*   from    tableName     A   where   rownum   <=   30)  B  where B.pageNo >=20;

Example:

--How to get the top 5 highest paid people in the employee table?

SELECT * FROM(
SELECT * FROM emp ORDER BY sal DESC)
WHERE Rownum<6

--How to obtain the information of the employee whose salary is ranked 5th in the employee table

SELECT * FROM(
SELECT * FROM emp ORDER BY sal DESC)
WHERE Rownum=5

--No content can be found in the query

--Same ROWNUM>=5 query 5 The results after the first place also failed

--This involves ROWNUM, which can only be less than but not greater than or equal to. So--an important obstacle to paging queries in Oracle database is this.

--As for the principle, ROWNUM is a pseudo column that must be arranged starting from 1 every time. So ROWNUM>=n. For example, it is not true if 1 is greater than any n (number of items). It can be found with ROWNUM=1. Because 1>=1 holds. N=2,1>=2 is not true. Therefore, only the first item can be checked for greater than or equal to. Plus less than or equal to. That’s all I can check. Therefore, this difficulty can be regarded as increasing the difficulty and obstacles of paging query.

--Method 1 The simplest query

--Articles 5 to 10.

--There is only one layer of nesting and one subquery,

SELECT * FROM
(SELECT ROWNUM rn ,e.* FROM emp e WHERE ROWNUM<=10)
WHERE rn>=5;

--This is actually transformed from the following method.

--That is, the new table obtained by the subquery is queried again.

--The key step is ROWNUM rn.

--And don't forget e.*, otherwise there will be no complete data.

SELECT * FROM (SELECT ROWNUM rn, e.*
          FROM emp e
         WHERE ROWNUM <= 10) table_1
 WHERE table_1.rn>= 5;

--The above is not sorted, the following is sorted

--Because it must be sorted first before querying, the larger the table, the slower the efficiency.

--Method 2

SELECT * FROM
(SELECT ROWNUM rm ,e.* FROM emp e order by sal DESC )
WHERE rm>=5 AND rm<=10

--The following is the method of using analytical functions;

--Method 3

SELECT * 
FROM (SELECT emp.*,
   ROW_NUMBER () OVER (ORDER BY sal DESC) rank 
    FROM emp)   
WHERE rank >=6 AND rank<=10;

--But our actual query Looking at the results, it turns out they are completely different.

--Checked manually. The results of the analytical function method are correct. Don't understand.

--Can anyone explain it?

--Here comes a meaningless

SELECT * FROM 
(SELECT e.*, ROWNUM AS rn from 
( SELECT * FROM emp ORDER BY sal DESC) e
)ee 
WHERE ee.rn>=5 AND ee.rn<=10

--Method 4 (massive data query, such as Baidu, Tmall query)

SELECT * FROM(
SELECT e.* ,ROWNUM rn
FROM (
SELECT *
FROM emp
ORDER BY sal DESC
) e
WHERE  ROWNUM<=10
)WHERE rn>=6;

--Decomposition steps

--The first step:

SELECT * FROM emp ORDER BY sal DESC

--The second step:

SELECT e .*,ROWNUM rn FROM
(SELECT * FROM emp ORDER BY sal DESC) e

--The third step:

SELECT e .*,ROWNUM rn FROM
(SELECT * FROM emp ORDER BY sal DESC) e
WHERE ROWNUM<=10

--The fourth step:

SELECT * FROM(
SELECT e .*,ROWNUM rn FROM
(SELECT * FROM emp ORDER BY sal DESC) e
WHERE ROWNUM<=10)
WHERE rn>=5

--Wrong guess.

SELECT e .*,ROWNUM rn FROM
(SELECT * FROM emp ORDER BY sal DESC) e
WHERE rn<=10

--To create an alias, you must query the data table.

--The query conditions are judged by conditions that do not yet exist, so they naturally fail.

--Method five, idle and boring usage

WITH ee AS (
  SELECT e.*, ROWNUM rn FROM
 ( SELECT * FROM emp ORDER BY sal DESC )e
  )
  SELECT ee.*
  FROM ee
  WHERE ee.rn>=5 AND ee.rn<=10

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What is the principle of oracle paging query. For more information, please follow other related articles on the PHP Chinese website!