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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.