[Oracle] Paging Query

Limited result set

In Oracle, it is very useful to use the ROWNUM field. Generally, you can use it to do two things:

1. Execute the top-N query. This is similar to the LIMIT Syntax of other databases. (Search for the first n pieces of data)

2. Perform paging queries, especially in stateless environments such as Web.

How ROWNUM works

ROWNUM is a virtual column in the query (not an actual column ). The value of ROWNUM is similar to: 1, 2, 3, 4,..., N. The ROWNUM value is not permanently allocated to a row. You cannot use ROWNUM = 5 to find a row.

Another important concept is when ROWNUM is assigned a value. ROWNUM is used after a Query statement passes the Query predicate stage but before the Query is sorted and summarized. Similarly, the value of ROWNUM can only grow after allocation.

The following statements do not have data.

select *   from t  where ROWNUM > 1;

The reason is: ROWNUM> 1 is incorrect for the first line. ROWNUM cannot be advanced to 2. Consider using the following method:

select ..., ROWNUM  from t where <where clause> group by <columns>having It can be viewed as processing in the following order:
1. FROM/WHERE clause goes first
2. ROWNUM is allocated and incremented from the from/WHERE clause to each output row.
3. SELECT applied
4. group by applied
5. HAVING is applied
6. order by is applied
This is why the following method of Query to process ROWNUM is problematic:
select *   from emp  where ROWNUM <= 5  order by sal desc;
This statement will randomly search for five pieces of data, instead of the five pieces of data with the highest salary.
Therefore, the correct method should be:
Sort first, and then use rownum.
select *  from  ( select *     from emp    order by sal desc )  where ROWNUM <= 5;
Query the first n data records
For a table with a large volume of data (millions or more) and a large amount of data in each record, the following methods can be used to obtain the first 10 or 100 pieces of data:
1. Query all data and obtain the first n orders
2. Use an internal table in combination with ROWNUM, similar
SELECT * FROM (your_query_here) where rownum <= N.
Except for a small amount of data retrieved from the database, the two methods differ greatly in performance. What is the difference?
Let's take a look at the execution process of the first method.
1. Full table Scan
2. Sort all data by field
3. If the sorting memory is not enough, extend it from the hard disk
4. Use the extended temporary memory to retrieve the first n data records
5. Release the temporary memory.
The process of the second method is much simpler.
1. Full table Scan
2. n pieces of data are obtained in the sorting area. Each piece of data after n pieces is compared with the n pieces. If conditions are met, the data is replaced.
That is to say, there are only n data entries in the sorting area. It will not be extended from the hard disk.
In the actual development process, with 20 million pieces of data,
ROWNUM is useless. If ROWNUM is used, the system response time is within 10 s.
Look at the example
Create an EMPLOYEE table and insert some data
CREATE TABLE EMPLOYEE(  EMPID varchar2(10),  EMPNAME varchar2(10),  SALARY varchar2(60));insert into EMPLOYEE values('001','zhao','7300');insert into EMPLOYEE values('002','qian','7400');insert into EMPLOYEE values('003','sun','7500');insert into EMPLOYEE values('004','li','7200');insert into EMPLOYEE values('005','zhou','7100');insert into EMPLOYEE values('006','wu','7000');insert into EMPLOYEE values('007','zheng','6500');insert into EMPLOYEE values('008','wang','6000');insert into EMPLOYEE values('009','feng','6100');insert into EMPLOYEE values('010','chen','6200');insert into EMPLOYEE values('011','zhu','6300');insert into EMPLOYEE values('012','wei','6400');insert into EMPLOYEE values('013','jiang','6700');insert into EMPLOYEE values('014','shen','6600');insert into EMPLOYEE values('015','han','6800');insert into EMPLOYEE values('016','yang','6900');
1. Now I want to view the five most paid data.
1) use:
Select * from EMPLOYEE where ROWNUM <= 5 order by SALARY desc;
Get:
2) select * from EMPLOYEE order by SALARY desc;
The results are completely different. The description of the above section is verified.
The correct statement can be:
select * from(select * from EMPLOYEE  order by SALARY desc) where ROWNUM <= 5 ;