SQL statement for querying Oracle table data by PAGE

SQL statement for querying Oracle table data by PAGE

The SQL syntax for querying Oracle data by page is widely used on the Internet. This article is also a typical syntax for the author to search on the Internet. It is not my original one and is posted here, this is purely to make the "Data paging topic series" look a little more complete and plump, so I would like to make a special statement here to avoid a buzz!

First, we will introduce two representative SQL instances for querying data by page.
• No order by sorting 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 retrieved data volume is large, it is almost unaffected and the speed is still high !)

SELECT *
FROM (select rownum as rowno, t .*
FROM emp t
WHERE hire_date BETWEEN TO_DATE ('20140901', 'yyyymmdd ')
AND TO_DATE ('20140901', 'yyyymmdd ')
And rownum <= 20) table_alias
WHERE table_alias.rowno> = 10;

• Order by sorting. (Most efficient)

(After testing, this method will get slower and slower as the search range expands !)

SELECT *
FROM (SELECT tt. *, rownum as rowno
FROM (SELECT t .*
FROM emp t
WHERE hire_date BETWEEN TO_DATE ('20140901', 'yyyymmdd ')
AND TO_DATE ('20140901', 'yyyymmdd ')
Order by create_time DESC, emp_no) tt
Where rownum <= 20) table_alias
WHERE table_alias.rowno> = 10;

Based on the two examples above, the following SQL statements represent the conventional paging retrieval format. You can freely change the format based on your actual needs and your individual proficiency in SQL, obtain the paging SQL statement you need.

SELECT *
FROM (SELECT a. *, ROWNUM rn
FROM (SELECT * FROM table_name)
Where rownum <= 40)
WHERE rn> = 21

The innermost retrieval SELECT * FROM TABLE_NAME indicates the original retrieval statement without page flip. ROWNUM <= 40 and RN> = 21 control the range of each page for paging retrieval.

The paging search 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 search 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 ROWNUM <= 40 is used to control the maximum value in the second layer of the search, the minimum value is controlled at the outermost layer of the search. The other method is to remove the where rownum <= 40 Statement on the second layer, and control the minimum and maximum values of pages at the outermost layer of the search. The search statement is as follows:

SELECT *
FROM (SELECT a. *, ROWNUM rn
FROM (SELECT * FROM table_name))
WHERE rn BETWEEN 21 AND 40

In most cases, the efficiency of the first search is much higher than that of the second one.

This is because in the CBO optimization mode, Oracle can push the outer retrieval conditions to the inner retrieval to improve the execution efficiency of the inner retrieval. For the first query statement, the retrieval condition where rownum <= 40 on the second layer can be pushed to the internal retrieval by Oracle. In this way, once the retrieval result of Oracle exceeds the ROWNUM limit, terminate the search and return the result.

The second search statement, because the search condition BETWEEN 21 AND 40 exists on the third layer of the search, oracle cannot push the layer-3 search conditions to the innermost layer (even pushing to the innermost layer makes no sense, because the innermost retrieval does not know what RN represents ). Therefore, for the second search statement, the innermost layer of Oracle returns all data that meets the conditions to the middle layer, and the middle layer returns all data to the outermost layer. Data filtering is completed at the outermost layer. Obviously, this efficiency is much lower than the first retrieval.

The search analyzed above is not only a simple search for a single table, but also effective for the case where the innermost retrieval is complex multi-table joint search or the innermost retrieval contains sorting.