Oracle paging query SQL principles and statements

Many of my friends use mysqllimit for paging to oracle and do not know how to write paging. In fact, oracle paging is similar to mssql paging. Next I will introduce oracle paging statements.

Many of my friends who use mysql limit for paging to oracle do not know how to write paging. In fact, oracle paging is similar to mssql paging. Next I will introduce oracle paging statements.

Many of my friends who use mysql limit for paging to oracle do not know how to write paging. In fact, oracle paging is similar to mssql paging. Next I will introduce oracle paging statements.

Simplest use

The Code is as follows:
T2 .* From (select rownum r, t1. * from youtable t1 where rownum Where t2.r>?

If the number is smaller than or equal to the maximum value, it is the number you need.

Query format:

The Code is as follows:
SELECT * FROM ( Select a. *, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) Where rownum <= 40 ) Where rn> = 21

SELECT * FROM TABLE_NAME indicates the original query statement that does not flip pages. ROWNUM <= 40 and RN> = 21 control the range of each page of the paging query.
The paging query statement given above has a high efficiency in most cases. The purpose of paging is to control the size of the output result set and return the result as soon as possible. In the preceding paging query statement, this consideration is mainly reflected in the where rownum <= 40 sentence.
There are two methods to select 21st to 40 records. One is that the second layer of the query in the example above uses ROWNUM <= 40 to control the maximum value, the minimum value is controlled at the outermost layer of the query. The other method is to remove the where rownum <= 40 Statement on the second layer of the query, and control the minimum and maximum paging values at the outermost layer of the query. Here is the query statement:

The Code is as follows:
SELECT * FROM ( Select a. *, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) ) Where rn between 21 AND 40

Later, we found that the query was slow as the data grew. Later, we obtained some test results and shared them with you.

-- 1: no order by statement. (Most efficient)
-- (After testing, this method has the lowest cost. Only one layer is nested, and the speed is the fastest! Even if the queried data volume is large, it is almost unaffected and the speed is still high !)

The Code is as follows:
SELECT * FROM (Select rownum as rowno, T .* From k_task T Where Flight_date between to_date ('20140901', 'yyyymmdd') and To_date ('20140901', 'yyyymmdd ') And rownum <= 20) TABLE_ALIAS WHERE TABLE_ALIAS.ROWNO> = 10;

-- 2: order by sorting. (Most efficient)
-- (After testing, this method slows down as the query range expands !)

The Code is as follows:
SELECT * FROM (select tt. *, ROWNUM AS ROWNO FROM (Select t .* From k_task T Where flight_date between to_date ('20140901', 'yyyymmdd') and To_date ('20140901', 'yyyymmdd ') Order by FACT_UP_TIME, flight_no) TT Where rownum <= 20) TABLE_ALIAS Where TABLE_ALIAS.rowno> = 10;

-- 3: no order by statement. (Method 1 is recommended)
-- (This method slows down as the query data size expands !)

The Code is as follows:
SELECT * FROM (Select rownum as rowno, T .* From k_task T Where Flight_date between to_date ('20140901', 'yyyymmdd') and To_date ('20140901', 'yyyymmdd') TABLE_ALIAS WHERE TABLE_ALIAS.ROWNO <= 20 AND TABLE_ALIAS.ROWNO> = 10; -- TABLE_ALIAS.ROWNO between 10 and 100;

-- 4: there is an order by sorting Statement (method 2 is recommended)
-- (This method slows down as the query range expands !)

The Code is as follows:
SELECT * FROM (select tt. *, ROWNUM AS ROWNO FROM (Select * From k_task T Where flight_date between to_date ('20140901', 'yyyymmdd') and To_date ('20140901', 'yyyymmdd ') Order by FACT_UP_TIME, flight_no) TT) TABLE_ALIAS Where TABLE_ALIAS.rowno BETWEEN 10 AND 20;

-- 5 alternative syntax. (Order by Statement)
-- (The syntax style is different from the traditional SQL syntax, which is not easy to read and understand. It is not recommended for standardization and unified standards .)

The Code is as follows:
With partdata ( Select rownum as rowno, TT. * FROM (Select * From k_task T Where flight_date between to_date ('20140901', 'yyyymmdd') and To_date ('20140901', 'yyyymmdd ') Order by FACT_UP_TIME, flight_no) TT Where rownum <= 20) Select * from partdata where rowno> = 10;

-- 6 alternative syntax. (No order by Statement)

The Code is as follows:
With partdata ( Select rownum as rowno, T .* From K_task T Where Flight_date between to_date ('20140901', 'yyyymmdd') and To_date ('20140901', 'yyyymmdd ') And rownum <= 20) Select * from partdata where Rowno> = 10;