Big Data Oracle Paging query

ROWNUM

It may be known that rownum only applies to less than or less than equals, and if it is equal to judgment, then it can only be equal to 1 and cannot be greater than the comparison.
RowNum is the number of rows that the Oracle system order is assigned to return from the query, the first row returned is assigned 1, the second row is 2, and so on.
RowNum always starts at 1, regardless of whether the current record satisfies the query result,rownum returns a value of 1, if the value of this record finally satisfies all conditions, then rownum will sliding scale, the next record RowNum will return 2, otherwise the rownum of the next record still returns 1.
Understanding this, it is clear why the general RowNum is greater than a value or equal to a value that is not 1 can not return the result, so for each record rownum is 1, and rownum is 1 does not satisfy the results of the query, so the rownum of the next record is not incremented, still 1, so all records do not meet the criteria.

Paged Query Format 1
Controls the minimum and maximum paging values at the outermost of the query. The query statements are as follows:

[SQL]View PlainCopy

1. SELECT * from
2. (
3. SELECT a.*, ROWNUM RN
4. From (SELECT * FROM table_name) A
5. )
6. WHERE RN between

Paged Query Format 2

[SQL]View PlainCopy

1. SELECT * from
2. (
3. SELECT a.*, ROWNUM RN
4. From (SELECT * FROM table_name) A
5. WHERE ROWNUM <= 40
6. )
7. WHERE RN >= 21

Paged Query Format 3
Given the multi-table syndication scenario, if you don't mind using hint in your system, you can rewrite the paging query statement to:

[SQL]View PlainCopy

1. SELECT/*+ first_rows */ * FROM
2. (
3. SELECT a.*, ROWNUM RN
4. From (SELECT * FROM table_name) A
5. WHERE ROWNUM <= 40
6. )
7. WHERE RN >= 21

Efficiency Issues
In contrast to these two types of notation,In most cases, a 2nd query is much more efficient than the 1th one.
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 2nd query statement, the second-level query condition where ROWNUM <= 40 can be pushed into the inner query by Oracle so that if the result of an Oracle query exceeds the ROWNUM limit, the result is returned by terminating the query.
and the 1th query statement, because the query condition between and 40 is present in the third layer of the query, and Oracle can not push the third layer of the query conditions to the most inner layer (even push to the inner layer is not meaningful, because the most inner query does not know what RN represents). Therefore, for the 1th 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, and obviously this is much less efficient than the first query.
The query analyzed above is not just a simple query for a single table, but is as effective for the most inner query as a complex multi-table union query or a case in which the inner query contains a sort.

Observing the execution plan of format 1 and format 22 above, it can be found that the only difference between the two execution plans is that the query of format 2 uses Stopkey in the count step, that is, Oracle pushes RowNum <= 20 into the query inner layer, Oracle ends the query when the record that meets the criteria for the query reaches the value of Stopkey. Therefore, it can be foreseen that in the second way, in the beginning of the page turn the query speed, the more to the back, the lower the efficiency, when turned to the last page, the efficiency should be the first way close.

A paged query statement can quickly return a result because its goal is to return the first result as quickly as possible. If there are 20 records per page, now to the 5th page, then only need to return to the first 100 records can meet the requirements of the query, and perhaps tens of thousands of records also meet the criteria of the query, but because of the paging limit, in the current query can ignore the data, and just as soon as possible to return the first 100 data. This is why the first_rows hint is often used in standard paged query statements.
For row operations, you can return the results directly to the previous layer call while the results are obtained. However, for the result set operation, Oracle must get all the data in the result set, so the rownum information in the paged query is not up or down. If more than one of these operations is included in the inner subquery, the paging query statement does not reflect any performance benefits: union, union All, minus, INTERSECT, GROUP by, DISTINCT, unique, and aggregate functions such as Max , Min, and analytic functions.

New features of oracle10gGroup by Stopkey, allowing oracle10g to solve the problem of inefficient paging of group by operations. Before 10g, theGROUP by operation of Oracle must be fully executed before the results can be returned to the user. However , oracle10g adds the group by Stopkey execution Path, which allows the user to abort a running operation at any time, depending on stopkey, while performing a group by operation. This allows the standard paging function to re-function for the GROUP by operation.

In addition to these operations, paged query also has a very obvious feature is that the smaller the number of pages processed, the higher the efficiency, the more to the back, the slower the query.
Paging queries are used to improve the return speed for the first n records with a smaller amount of data. Whether it's an index scan, a NESTED loop connection, or an order by Stopkey, these methods bring performance gains as a small amount of data, and once paged to the last few pages, you'll find that these methods have not only improved performance, but also have a much lower performance than normal queries. This point, in the use of paging query, must be in mind.
Paging queries in general, rarely will be turned to the last article, if only occasionally encountered this situation, the system performance will not have a great impact, but if often encountered this situation, in the design of paging query should be given enough consideration.

Problems caused by the not-unique sequence of rows
If the column used for sorting is not unique, that is, there are rows with equal values, which can cause the first 10 records to be returned, a row of data appears, and the second time in the 11 to 20th record, a row of data appears again. A data repeat of two times will inevitably mean that there is no data in two queries.
In fact, the cause of this problem is very simple, because the row sequence is not the only cause. The sorting algorithm used here by Oracle is not stable, that is, for data with key values that are equal, this algorithm does not guarantee the order in which the data of these key values are equal before the sorting is done.
Solving this problem is also very simple. There are two ways to consider this.
1) When sorting with a field that is not unique, followed by a unique field.
Generally after the sort field followed by a primary key is OK, if the table does not have a primary key, and rowID can also. This method is the simplest and has the least impact on performance.
2) Another method is to use the between and method that has been given many times before .
In this way, because of the full ordering of the table data, only a subset of the data in the full order is taken at a time, so there is no duplicate data problem mentioned above.
But it is because of the use of the full sort, and rownum information can not be pushed into the query inside, resulting in a very inefficient execution of this writing

Reprint: http://blog.csdn.net/fw0124/article/details/42737671

Big Data Oracle Paging query