Explain the example of Oracle analysis function (using row_number as the background page), using lerow_number

Explain the example of Oracle analysis function (using row_number as the background page), using lerow_number

When talking about Oracle's analysis functions, the most common functions are row_number, rank, and dense_rank. The specific usage has been written in countless articles and won't be repeated, here we only describe the use scenario of one row_number.

As we all know, paging is a complicated problem. If the data volume is large, the front-end cache performance will be poor. Generally, we will consider the number of pages in the background. In this case, we need to provide paging support for the database. The oracle database does not have the limit syntax in MYSQL. Therefore, subqueries are generally used for paging. Next, try several common SQL paging statements and create a test data table first, write 1 million pieces of data for testing.

Set 2 variables, N_COUNTPERPAGE for each page, and take the N_PAGE data. Suppose N_COUNTPERPAGE = 10, N_PAGE = 70001, And the sn should be retrieved in (700001 ~ 700010) data

The first SQL statement:

The second method is changed to the row_number () function.

These two methods calculate the column number to be obtained through the number of pages, and then obtain the calculated page data directly from the table. The following is a more intuitive method, it is to split the page according to the N_COUNTPERPAGE data in each row, and then extract the data in the N_ROW row of the N_PAGE:

From the above several methods, the speed of pagination with row_number is not improved. Many people think that using this advanced function will significantly improve the performance, and it is only subjective judgment. The speed of querying by PAGE and then getting the number is definitely not as fast as the method for calculating the row number.

In addition, there is an inevitable disadvantage in the background paging, that is, frequent data changes may lead to data omission and overlap. For example, after obtaining 1st pages, the background deletes all data on 1st pages, the next step is to retrieve 2nd pages (actually the previous 3rd pages), and the original 2nd page data will be missed.