MySQL and Oracle Paging

https://my.oschina.net/realfighter/blog/349867

Link Address: http://www.xx566.com/detail/143.html

Previously summed up an article, Oracle paging query statement optimization, at that time on the Oracle paging statement also took a bit of time to remember, but today in the interview, the test of different databases of the paging SQL statement, the writing of Oracle database has a problem, Think very familiar with the page of SQL writing is also a lot of unfamiliar, here continue to summarize and tidy up, deepen memory.

MySQL is the most simple paging relative to the Oracle database, and it is easy to implement pagination by providing the Limit keyword, as follows:

SELECT  *  from testtable WHERE 1 = 1  1;

Oracle's paging SQL, traditionally through rownum, is paginated, as follows:

SELECT  * from(SELECTT1.*, ROWNUM RN from(SELECT              *             fromtesttableORDER  byIdDESC) T1WHEREROWNUM<=  -)WHERERn> 0;

However, the above page of SQL in the large amount of data, the more backward the paging query will be slower, there is another efficient paging query, through rownum and rowid for paging, as follows:

SELECTt1.* fromTestTable T1, (SELECTRID from(SELECTROWNUM RN, T.rid from(SELECTROWID RID fromtesttableWHERE 1 = 1) TWHEREROWNUM<=  -)                    WHERERn> 0) T2WHERE 1 = 1  andT1. ROWID=T2.rid;

Summary: The application of paging in a variety of systems, is an essential part of the application and optimization of paging SQL has always been an important component of the program development, need to constantly memory and summary.

MySQL and Oracle Paging