Comparison of paging queries between MySQL and Oracle

MySQL Paging query statement

The index of m for rows in the following query statement defaults from 0 to a cursor 0 in PL/SQL that starts with the first bar n indicates how many data to query backwards.

For example:

SELECT * from EMP limit 2,3 to query the third data to the sixth data (after the packet is not wrapped) that is, the third data including the third data does not include the sixth data is 3, 4, 5 records

SELECT * from Buss_stu_info limit m,n;

Oracle Database Paging Query

A layer of paged query:

The amount of timely data is large enough to almost not be affected by the query rate is fast enough, generally in writing such SQL statement is a person to divide the SQL statement into two layers, the inner layer, the outer layer, in the inner layers less than the largest, the outer layer is greater than the smallest, (maximum minimum you to set)

SELECT * FROM (select RowNum as Rn,empno,ename from emp e where rownum<10) Ta where ta.rn>5;

Two-level paging query:

Oracle's paging query statements can be applied basically in the format given in this article .

Paged Query format:

SELECT *

Span style= "color: #000000; line-height:36px;" >   from   (select  a .*,  rownum  rn

            from   (select   *

                     From  table_name )  a

            where  rownum  <=  40 )

WHERE rn >=

The most internal query, select * FROM table_name, represents the original query statement without paging. ROWNUM <= 40 and RN >= 21 control the range of pages per page for paged queries.

The paging query statement given above has high efficiency in most cases. The purpose of paging is to control the output result set size and return the results as soon as possible. In the above paged query statement, This consideration is mainly reflected in the where ROWNUM <= 40 sentence .

There are two ways to select the 21st to 40th record, one of which is shown in the above example in the second layer of the query through the rownum <= 40来 control The maximum value, at the outermost level of the query control the minimum value. The other way is to remove the ROWNUM <= 40 statement that queries the second layer, controlling the minimum and maximum paging values at the outermost level of the query. This is the query statement as follows:

SELECT *

  From (SELECT a. *, ROWNUM RN

             From   (select   *

                 From table_name) a )

  WHERE rn between and +

In contrast to these two formulations, the first query is much more efficient than the second in most cases.

This is because in the CBO optimization mode, Oracle can push the outer query condition into the inner query to improve the execution efficiency of the inner query. For the first query statement, the second level of the query condition where ROWNUM <= 40 can be pushed into the inner query by Oracle, so that if the results of an Oracle query exceed the ROWNUM limit, the result is returned to the terminating query.

and the second query statement, because the query condition between and 40 is present in the third layer of the query, and Oracle cannot push the third layer of the query conditions to the most inner layer (even pushing to the inner layer is meaningless, because the most inner query does not know what RN represents). Therefore, for the second query statement, the Oracle's inner layer is returned to the middle tier as all data that satisfies the criteria, and the middle tier returns to the outermost of all the data. Data filtering is done at the outermost layer, which is obviously much less efficient than the first query

This article is from the "Essays" blog, make sure to keep this source http://lishouxiang.blog.51cto.com/8123559/1854621

Comparison of paging queries between MySQL and Oracle