oracle-Paged Query Scheme

I. Use RowNum to do three-layer packing inquiry (common scheme)

SELECT *  from (Select A.* from (select* from)  where<=)where>=

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.

Two. Using between. and relationship

SELECT *  from (Select A.* from (select* from TABLE _name) A)WHEREbetween and

In the majority of cases, the efficiency of the first query is much higher than the second one, compared to the above two formulations.

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, 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.

Three. rowID Paging

Select *  fromT_xiaoxiwhererowIDinch(SelectRid from(SelectRowNum Rn,rid from(SelectrowID rid,cid fromT_xiaoxiOrder  byCiddesc)whereRowNum<10000)whereRn>9980)Order  byCiddesc;

Four. According to the analysis function to divide

Select *  from (select T.*over (orderbydescfromwhere RK<10000 and RK>9980;

oracle-Paged Query Scheme