Detailed description of SQL Server and Oracle paging queries, SQL Server Oracle

Detailed description of SQL Server and Oracle paging queries, SQL Server Oracle

Whether it is the implementation of the paging query code in DRP or the paging query result in the interview questions, I am prompted that paging query is important. When the data volume is large, you must consider it. I have never taken the time to stop and summarize it. Now I read the paging query content in the Oracle video again, and it is easy to understand.

1. paging algorithm
When I first searched for information on the Internet, I found a lot of paging content, which was quite messy. This is not the case. The online materials are similar. The problem lies in myself. I don't understand what is the premise for paging? We all know that these variables are involved as long as there is a page: the number of records per page (pageSize), the current page (pageNow), the total number of records (totalRecords), and the total number of pages (totalPages), start page (beginRow), end page (endRow ). The paging algorithms on the Internet use pageSize, beginPage, and endPage. In fact, these variables need to be classified: I divide themCategory 3:
A. What needs to be queried from the database: totalRecords. "select count (*) from tableName"
B. the most basic requirements are pageSize and pageNow. (I personally think this is the premise of the paging algorithm)
C. Calculated from other variables: totalPages, beginRow, and endRow. (here we need to calculate that beginRow and endRow are used in paging queries, and totalPages are the information to be provided on pages ). Specific formula:

totalPages: if ((totalRecords% pageSize) == 0) {               totalPages = totalRecords/ pageSize;             } else {               totalPages = totalRecords/ pageSize + 1;             } beginRow: (pageNow-1) * PageSize +1 endRow:   pageNow * PageSize 

In this way, the values of these variables can be obtained. For details about how to use it, see sections 2 and 3.

2. Common paging methods in Oracle
In fact, whether it is Oracle or SQLServer, the basis for implementing paging queries is subqueries. In my own words: select sets select.
There are three paging methods for Oracle. I will only talk about one easy to understand. Take the employee table (emp) as an example. If there are 10 records, five records are required on each page. If the current page is 2, 6-10 records are queried. We first use a specific number and then change it to a variable.
Oracle implementation step 1:Select a. *, rownum rn from (select * from emp) a; where rownum is the internal distribution row number of Oracle. Select * from emp in parentheses is used to query all records in the emp table. Then we can use the query results as the view for further query. In addition to querying all emp, select adds a rownum for later query.
Step 2 of Oracle implementation:Select a. *, rownum rn from (select * from emp) a where rownum <= 10; the second step adds a condition to query records with a travel number less than or equal to 10. The following may be a question: why do not write rownum> = 6 and rownum <= 10. The Oracle internal mechanism does not support this method.
Step 3 of Oracle implementation:Select * from (select. *, rownum rn from (select * from emp) a where rownum <= 10) where rn> = 6; OK, you can query 6-10 records.
Last. We convert it to a variable. It may be in java or pl/SQL.
Three more to be converted:"The location of emp is the specific table name, the location of "6" is (pageNow-1) * PageSize + 1, the location of "10" is pageNow * PageSize.
This method can be used as a template, which is easy to modify. You only need to change the bottommost layer for all changes. For example, when querying a specified column: Modify the base-layer select ename, sal from emp; sort by salary column: select ename, sal from emp order by sal; you only need to modify the innermost layer.

3. Common paging methods in SQLServer
We still use the employee table example to illustrate the implementation of paging in SQLServer.
Use of the first type of TOP:
SQL Server implementation step 1: select top 10 * from emp order by empid; sort by employee ID in ascending order and retrieve the first 10 records.
Step 2 of SQLServer implementation: select top 5 * from (select top 10 * from emp order by empid) a order by empid desc. Sort the 10 records in descending order of employee numbers and then extract 5 Records. This is the first time in ascending order, and the second time in descending order is clever. I didn't expect top to achieve this effect. The position 10 here is replaced by the variable pageNow * PageSize, and 5 by PageSize.
Use of the second Top and In:
Select top 5 * from emp where empid in (select top 10 empid from emp order by empid) order by empid desc; the position 10 here is replaced by the variable pageNow * PageSize, and 5 by PageSize.
Other queries are similar, so we will not repeat them here.

The above is an example of implementing the paging function for two databases. I hope this will be helpful for your learning.