Oracle paging Query

Select ename from

(

Select ename, rownum rn from

(Select * from EMP order by empno)

Where rownum <10

)

Where rn> = 4;

 

This is because in the CBO optimization mode, Oracle can push the outer query conditions to the inner query to improve the execution efficiency of the inner query. For the first query statement, the SQL query condition where rownum <= 40 can be pushed to the inner query by Oracle. In this way, once the query result of Oracle exceeds the rownum limit, the query is terminated and the result is returned.

The second query statement, because the query conditions between 21 and 40 exist on the third layer of the query, oracle cannot push the layer-3 query conditions to the innermost layer (even pushing to the innermost layer makes no sense, because the innermost layer query does not know what rn represents ). Therefore, for the second query statement, the oldest layer of Oracle returns all the data that meets the conditions to the middle layer, and the data that the middle layer returns to the outermost layer is all the data. Data filtering is completed at the outermost layer. Obviously, this efficiency is much lower than the first query.

 

 

Rownum pseudo column:
1. Use the pseudo column rownum to query the first N records returned by the SELECT statement based on specific conditions. Rownum is to add a pseudo column to the result set. It first finds the result set and then adds a column. It is the serial number of the result that meets the condition. It can be sorted from 1 and can only be used <>=! = These comparisons are consistent.
RownumEqualQuery conditions for a value. For example, you can use rownum = 1 as the condition to query the first record. However, if rownum = 2 is entered for the second record, no data is found, the = of rownum is valid only for 1;
2. Similarly, queryGreaterThe query condition for a value, rownum> N (Natural Number of n> 1) is not true. How can we query records after the first row?
Solution: solves the problem with subqueries, but the alias must be set for rownum, "select * from (selectRownum No
, ID, name from Table1) where no> 1 ";
3. rownumLessQuery of a value is acceptable. Select * From Table1 where rownum <3
4. rownum and sorting
In Oracle, rownum is the sequence number generated when data is retrieved. To specify rownum row data for the specified sorted data, you need to process it:
"Select rownum, ID, name from Table1 order by name", the query results are sorted by name, but rownum is not sorted by 1, 2, 3... in the order of record insertion;
Solution: Use subquery, Select rownum, ID, name from (select * From Table1 order by name)