Summary of paging query for oracle, mysql, and SqlServer Databases
- Database
|
- Paging query statement
|
- Description
|
- MySql
|
- "QUERY_ SQL limit ?,? "
|
- Use the limit keyword, the first "? "Is the starting line number,
- The second "? "Is the number of returned entries
|
- Oracle
|
- SELECT * FROM
- (Select a. *, ROWNUM RN FROM
- (QUERY_ SQL) A WHERE ROWNUM
- <= ?) Where rn> =?
|
- Using the rownum keyword and nested layer-3 select
- Statement implementation. First "? "Indicates the end row number,
- The second "? "Indicates the actual row number.
|
- SQL Server
|
- No general statements
|
- You can use top n to return the first n records or use stored procedures.
|
- DB2
|
- Suppose the query statement: select t1. * from t1 order
- By t1.id; the paging statement can be:
- "Select * from (select rownumber () over
- (Order by t1.id) as row _, t1. * from t1
- Order by t1.id) as temp _ where row _
- Between? + 1 and? "
|
- Two "? "Between records
|
- InterBase
|
- "QUERY_ SQL row? To ?"
|
- Two "? "Between records
|
- PostgreSQL
|
- "QUERY_ SQL limit? Offset ?"
|
- First "? "Is the starting line number, the second "? "Representative
- Number of returned records
|
MySql:
The implementation page of MySQL database is relatively simple, and the LIMIT function is provided. Generally, you only need to write the SQL statement directly.
The LIMIT substatement can be used to LIMIT the number of data returned by the SELECT statement. It has one or two parameters. If two parameters are provided, the first parameter specifies the position of the first row returned in all data, starting from 0 (note not 1). The second parameter specifies the maximum number of returned rows. For example:
Select * from table LIMIT 10; # Return the first 10 rows
Select * from table LIMIT; # Return the first 10 rows
Select * from table limit 5, 10
;# Return data in rows 6-15. The first parameter indicates the start point, and the second parameter indicates the number of data entries displayed on each page. Note: The first page is represented by 0.
Oracle:
Considering the implementation paging in mySql, the select * from table name limit starts to show the number of records and how many records can achieve our paging effect.
However, there is no limit keyword in oracle, but the rownum field exists.
Rownum is a pseudo column. It is the number automatically assigned to each row in the query result by the oracle system. The first row is 1, the second row is 2, and so on ....
First:
The Code is as follows: SELECT * FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
Where rownum <= 40
)
Where rn> = 21
SELECT * FROM TABLE_NAME indicates the original query statement that does not flip pages. ROWNUM <= 40 and RN> = 21 control the range of each page of the paging query.
The paging query 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 query 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 the second layer of the query in the example above uses ROWNUM <= 40 to control the maximum value, the minimum value is controlled at the outermost layer of the query. The other method is to remove the where rownum <= 40 Statement on the second layer of the query, and control the minimum and maximum paging values at the outermost layer of the query.
Second:
The Code is as follows:
Select * from (select e. *, rownum r from (select * from emp order by sal desc) e) e1 where e1.r> 21 and e1.r <= 40;
RED: sort by salary in descending order and query all information.
Brown part: obtain the value queried by the red department, query the rownum of the system, and specify the upper alias. This sentence is critical and plays a transitional role. First, you must calculate rownum to specify the sequence number for the red part, or use this variable for the blue part. Specify the number of start records and the condition for ending records.
Blue part: specify the number of records from the beginning to the end, and retrieve the value of the brown Department as the query condition variable.
Conclusion: In most cases, the efficiency of the first query is much higher than that of the second query.
SqlServer:
Paging solution 1(Use Not In and select top pages)
Statement format:
The Code is as follows:
Select top 10 * FROM TestTable WHERE (id not in (select top 20 id FROM TestTable order by id) ORDER BY ID
Select top page size * FROM TestTable WHERE (id not in (select top page size * Page id FROM Table order by id) ORDER BY ID
Paging solution 2(Select top pages with the ID greater)
Statement format:
The Code is as follows:
Select top 10 * FROM TestTable WHERE (ID> (select max (id) FROM (select top 20 id FROM TestTable order by id) as t) ORDER BY ID
Select top page size * FROM TestTable WHERE (ID> (select max (id) FROM (select top page size * Page id FROM Table order by id) as t) ORDER BY ID
Paging solution 3(Paging using the SQL cursor Stored Procedure)
The Code is as follows:
Create procedure XiaoZhengGe
@ Sqlstr nvarchar (4000), -- query string
@ Currentpage int, -- page N
@ Pagesize int -- number of lines per page
As
Set nocount on
Declare @ P1 int, -- P1 is the cursor id
@ Rowcount int
Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
Select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages --, @ rowcount as total number of rows, @ currentpage as current page
Set @ currentpage = (@ currentpage-1) * @ pagesize + 1
Exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize
Exec sp_cursorclose @ P1
Set nocount off
Other solutions: If there is no primary key, you can use a temporary table or solution 3, but the efficiency will be low.
We recommend that you add primary keys and indexes during optimization to improve query efficiency.
The SQL query Analyzer displays comparison: conclusion:
Paging solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement
Paging solution 1: (using Not In and select top pages) The efficiency is second, and SQL statements need to be spliced.
Paging solution 3: (using SQL cursor Stored Procedure paging) The efficiency is the worst, but the most common
In actual situations, specific analysis is required.
========================================================== ======================================
Database paging (oracle uses the parsing function row_number for efficient paging)
Mysql uses the limt keyword for paging
Select * from t_order limit 5, 10;# Return data in rows 6-15. The first parameter indicates the start point, and the second parameter indicates the number of data entries displayed on each page. Note: The first page is represented by 0.
Select * from t_order limit 5;# Return the first five rows
Select * from t_order limit 0, 5;# Return the first five rows
Mssql 2000 uses the top keyword for paging (the keyword rownum is also supported in Versions later than 20005)
Select top 10 * from t_order where id not in (select id from t_order where id> 5); // 6th to 15 rows of data are returned
10 indicates that 10 records are taken. 5 indicates that the data is retrieved from the first 5th records.
Oracle Paging
① Use the rownum keyword (layer-3 nesting)
SELECT*
FROM(
SELECTA .*,
ROWNUMNum
FROM(
SELECT*
FROMT_order)
WHERE ROWNUM<= 15)
WHERENum> = 5;
--Rows 5-15 are returned.
② Use the row_number resolution function for paging (more efficient)
SELECTXx .*
FROM(
SELECTT. *, row_number () over (
ORDERO_id)
ASNum
FROMT_order t) xx
WHERENum
BETWEEN5
AND15; -- Return Row 5-15 data
Formats available for parsing functions
Function () over (pertion by field order by field );
Pertion partitions by a Field
Order is sorted by various fields
Paging Query
The database table structure and records are as follows:
1. Based on rowid:
16:31:48 SQL> select * from passvehicleinfo p where rowid in (select rid from (select rownum rn, rid from (select p. rowid rid, p. passvehicleid from passvehicleinfo p order by p. passvehicleid desc) view1 where rownum <10000) view2 where rn> 9980) order by p. passvehicleid asc;
2. Score by Analysis Function
17:02:42 SQL> select * from (select p. *, row_number () over (order by p. passvehicleid desc) rk from passvehicleinfo p) where rk> 9980 and rk <10000;
3. Divide by rownum
17:07:38 SQL> select * from (select view1. *, rownum rn from (select p. * from passvehicleinfo p order by p. passvehicleid desc) view1 where rownum <10000) view2 where rn> 9980;
The third type is introduced below: By rownum
1. rownum Paging
SELECT * FROM emp;
2. Display rownum [allocated by oracle]
SELECT e. *, ROWNUM rn FROM (SELECT * FROM emp) e;
Rn is equivalent to the ID of the row allocated by Oracle.
3. Select 6-10 records
First, 1-10 records are found.
SELECT e. *, ROWNUM rn FROM (SELECT * FROM emp) e where rownum <= 10;
If rownum> = 6 is not followed,
4. Check 6-10 records.
SELECT * FROM (SELECT e. *, ROWNUM rn FROM (SELECT * FROM emp) e where rownum <= 10) WHERE rn> = 6;
5. Several query changes
A. Specify the query column. You only need to modify the subquery at the innermost layer.
Only query employee numbers and salaries
SELECT * FROM (SELECT e. *, ROWNUM rn FROM (SELECT ename, sal FROM emp)
E where rownum <= 10) WHERE rn> = 6;
B. Sort query. You only need to modify the subquery at the innermost layer.
Query 6-10 pieces of data after sorting the salary
SELECT * FROM (SELECT e. *, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER
By sal) e where rownum <= 10) WHERE rn> = 6;
Paging is often used to display record entries. A common method is to rewrite the original query statement using the location interface provided by various databases to retrieve only some records in a specific range. The query and location interfaces for different databases are different. The following is a summary:
SQL Server
There are a lot of information about paging SQL, some use stored procedures, and some use cursors. I don't like using cursors. I think they are cost-effective and inefficient. Using Stored procedures is a good option, because stored procedures are pre-compiled at the end of the process, and execution efficiency is high, it is also more flexible. Let's take a look at the paging SQL of a single SQL statement.
Method 1:
Applicable to SQL Server 2000/2005
Select top page size * FROM table1 WHERE id not in (select top page size * (page-1) id FROM table1 order by id) order by id
Method 2:
Applicable to SQL Server 2000/2005
Select top page size * FROM table1 WHERE id> (select isnull (MAX (id), 0) FROM (select top page size * (page-1) id FROM table1 order by id) a) order by id
Method 3:
Applicable to SQL Server 2005
Select top page size * FROM (SELECT ROW_NUMBER () OVER (order by id) AS RowNumber, * FROM table1) a where RowNumber> page size * (page number-1)
Note: page size: the number of lines per page; page number: page number. During use, replace "page size" and "page size * (page size-1)" with a digital number.
MYSQL
SELECT * from tt limit 1, 20
SELECT * from tt limit 21,30
/*
If you have tens of thousands of data, you can directly use the limit Function of mysql. If it is more than 1 million of data, you may have to explain the method, next we will create a paging query statement for millions of data records.
Mysql> select * from news where id> = (select id from news limit 490000, 1) limit 10; // 0.18 sec // obviously, this method wins.
Mysql> select * from news limit 490000,10 // 0.22 sec;
*/
The following article mainly introduces the actual MySQL paging operation solution. In fact, the simplest way to implement MySQL paging is to use the mysql database LIMIT function, LIMIT [offset,] rows can search for N records starting from the M records in the MySQL database table:
SELECT * FROM table name limit m, Nselect * from sys_option limit 10, 20 select * from table [query condition] order by id limit ?,?
Oracle
The paging query statement of Oracle can basically follow this article. The next article will explain it through examples. Next we will briefly discuss the situation of Multi-table join. For JOIN queries of the most common equivalent tables, CBO may use two JOIN Methods: nested loop and hash join (merge join is less efficient than hash join, which is not considered by CBO in general ). Because paging is used, the maximum number of records returned is specified. When the number of returned records exceeds the maximum value, the nested loop can immediately curb and return the results to the central layer, however, hash join must process all the replica sets (so does merge join ). In most cases, it is highly efficient to select nested loop as the query connection method for paging queries (most of the time when querying by page is the data of the first few pages, the lower the page number, the lower the access probability ).
Therefore, if you don't mind using HINT in the system, you can rewrite the paging query statement:
SELECT/* + FIRST_ROWS */* FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
Where rownum <= 40
)
Where rn> = 21
