There are three ways to pagination with SQL and Oracle databases!
Let's take a look at how the two methods are implemented if we want to get data from 1000th to 1010th in the database.
1. Methods of using temporary tables. (The main thing in the system is to write SQL statements directly)
A) Arrange the order by the order you want
b) Create a temporary table
c The data from article No. 0 to 1010th is removed from the database
D. Put the data into a temporary table
(e) Arrange the order of the temporary table by the reverse way of a.
f) and then simply display the first 10 of the temporary table.
(g) Destruction of temporary tables
2. Method of using Object
A) Arrange the order by the order you want
b from the database to remove No. 0 to 1010th of the data
(c) Pour the 10 from these 1010 data into an object
D) completely invert the record in this object.
E to show the data in object
Obviously the second method is better than the first one. It reduces the resources needed to create and destroy temporary tables, but they all share a common weakness. That is, they have to take out of the database No. 0 to 1010th of the data so that the query out of the number of records, but the amount of data transmission network is very large!
Therefore, a better paging approach should be:
Retrieve data from a block of page size only from the database each time you page. In this way, although each page will need to query the database, but the number of records to query out a small amount of data transmission network, if the use of connection pool can be skipped the most time-consuming to establish the database connection process. At the database end, there are a variety of mature optimization techniques used to improve the query speed, than in the application server layer cache more effective.
For SQL Server databases to get 第1000-1010条 records:
Select Top * FROM (
Select Top * FROM (
Select Top 1010 * out Docdetail ORDER by lastmodidate ASC, Id ASC
) temptbl1 ORDER BY lastmodidate Desc, Id desc
) temptbl2 ORDER BY Lastmodidate Asc,id ASC
For Oracle databases to get 第1000-1010条 records because rownum in Oracle is assigned before the query is sorted. So the corresponding writing should be:
SELECT * FROM (
Select My_table.*, rownum as Temptbl_rownum from (
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.
