Oracle row_number () Efficient Paging

Using the built-in function paging will certainly be more efficient. This article introduces an efficient paging Method for Oracle row_number (). For more information, see.

Step 1: Use rownum first

The Code is as follows:

Copy code

-- Pagination row_number, not rownum -- Sort by n_count from large to small, 3 entries per page Select rownum r, t. * FROM t_news t Where rownum <= 3 Order by t. n_count DESC -- Question: Why is the row number messy after order? Select rownum r, t. * FROM t_news t -- Cause: the row number is allocated first, and then sorted by n_count. -- So it must be sorted and the row number is generated. Select rownum r, t. * FROM ( SELECT t. * FROM t_news t order by t. n_count DESC) t -- Paging -- Err Select rownum r, t. * FROM ( SELECT t. * FROM t_news t order by t. n_count DESC) t WHERE r between 1 AND 3 -- 1st page Select rownum r, t. * FROM ( SELECT t. * FROM t_news t order by t. n_count DESC) t Where rownum between 1 AND 3 -- 2nd page Select rownum r, t. * FROM ( SELECT t. * FROM t_news t order by t. n_count DESC) t Where rownum between 4 AND 6 -- Error: ROWNUM must start from 1! SELECT k. * FROM ( Select rownum r, t. * FROM ( SELECT t. * FROM t_news t order by t. n_count DESC) t ) K WHERE r BETWEEN 4 AND 6

-- Trouble, low efficiency!

* *** Step 2: Use the row_number () over () function.

The Code is as follows:

Copy code

Select t2. * from (Select t. *, row_number () over (order by t. n_count desc) orderNumber from t_news t order by t. n_count desc) t2 where orderNumber between 1and 3; **************************************** **************************************** **************************************** ****************** 88 SELECT * FROM ( SELECT t. *, row_number () over (order by n_count DESC) r FROM t_news t Order by t. n_count DESC ) T WHERE r BETWEEN 4 AND 6

-- General Syntax: resolution function () over (partition by field order by field)