oracle paging query statement

Oracle common SQL paging implementation

There are many ways to implement paging with SQL in Oracle, but some statements may not be very common and can only be used in some special scenarios

Below are three common implementations of the following ROWNUM is the core keyword in the following implementations. ROWNUM is a virtual column that takes the value of the serial number of the total number of records

First of all, let’s introduce one of the most common implementations that we use in our work

SELECT*

FROM(SELECTROW_*ROWNUMROWNUM_

FROM(SELECT*

FROMTABLE

WHERETABLE_ID=XX

ORDERBYGMT_CREATEDESC)ROW_

WHEREROWNUM<=)

WHEREROWNUM_>=;

The innermost of these queries SELECT for the original query statement does not turn the page can be replaced with any of their own SelectSQL ROWNUM<= and ROWNUM>= control paging query for each page of the range

Paging the purpose is to control the size of the output result set will be as soon as possible to return to the results of the above SQL statement in most cases have a higher efficiency mainly reflected in the WHEREROWNUM<= on this line so that the query process to control the maximum number of records

The above example shows in the second level of the query through the ROWNUM<= to control the maximum value of the query in the query to control the minimum value of the outermost level of the query, while another way is to remove the query of the second level of the WHEROWNUM<= statement in the query in the query of the outermost level of the minimum and maximum control paging control the minimum and maximum values of the paging at this point the SQL statement is as follows which is the second implementation to be introduced

SELECT*

FROM(SELECTA*ROWNUMRN

FROM(SELECT*

FROMTABLE

WHERETABLE_ ID=XX

ORDERBYGMT_CREATEDESC)A)

WHERERNBEEENAND;

Since Oracle can push the outer query condition into the inner query to improve the execution efficiency of the inner query but not across multiple tiers

For the first query statement the second tier of the query condition WHEREROWNUM<= can be pushed into the inner query by Oracle so that the results of the Oracle query are returned once the ROWNUM constraint is exceeded

The second query statement is not able to push the BEEENAND query condition into the inner query because the query condition BEEENAND exists at the third level of the query, which is the third level of the query. The second query statement because the query condition BEEENAND is present in the third level of the query and Oracle can not push the third level of the query condition to the innermost level (even if it is pushed to the innermost level there is no point because the innermost query does not know what the RN stands for) Therefore, for the second query statement Oracle innermost level returns to the middle level all the data that meets the conditions while the middle level returns to the outermost level all the data filtering of the data in the outer level to complete the obvious efficiency of this query than the first query is much lower

Above two options are completely through the ROWNUM to complete the following one is the use of ROWID and ROWNUM combination of SQL statements are as follows

SELECT*

FROM(SELECTRID

FROM(SELECTRRIDROWNUMLINENUM

FROM(SELECTROWIDRID

FROMTABLE

WHERETABLE_ID=XX

ORDERBYGMT_CREATEDESC)R

WHEREROWNUM<=)

WHERELINENUM>=)T

TABLET

WHERETRID=TROWID;

From the statement there is a total of layers of Select nested query the innermost layer is the original SQL statement that can be replaced without paging, but the only field that is queried is the ROWID without any specifics of the actual table field to be queried. The query for the actual field value is implemented at the outermost level

The principle of this approach is roughly as follows: first, the ROWNUM is queried for the ROWID of the actual row returned after paging, and finally the final return field value is queried for by the ROWID and returned

Compared to the previous two implementations, this SQL is more cumbersome to implement and is not very versatile, because the original query statement has to be split into two parts, which is not very useful. split the original query into two parts (the query field in the outermost table and the query condition in the innermost)

But this implementation has advantages in certain scenarios, such as when we often have to page to the very back, for example, we often need to look up a record and its subsequent data, and then this solution may be more efficient than the previous one

Because the previous one is based on the ROWNUM

Because the previous solution is controlled by ROWNUM, you need to query for a row and then fetch the data between the last rows, whereas this solution fetches the data you need directly from the ROWID

From the point of view of constantly flipping backward, the cost of the first solution grows more and more and basically grows linearly, whereas the cost of the third solution doesn’t grow as fast as the previous solution does, and it only grows by reading the ROWID portion of the queries

The third solution does not grow as fast as the previous one. ROWID

Of course, in addition to the above mentioned options we can also use the following SQL to achieve

SELECT*

FROMTABLE

WHERETABLE_IDNOTIN

(SELECTTABLE_IDFROMTABLEWWORKTABLE_IDNOTIN

(SELECTTABLE_IDFROMTABLEWORKTABLE_IDFROMTABLE). IDFROMTABLEWHEREROWNUM<=)

ANDROWNUM<=;

SELECT*

FROMTABLE

WHEREROWNUM<=

MINUS

SELECT* FROMTABLEWHEREROWNUM<=;

………………

Note that when ROWNUM is used as the When ROWNUM is used as a query condition he is executed before ORDERBY so be careful

For example, if we want to query the previous rows of a TABLE sorted in reverse order by TABLE_ID we can’t do it with the following SQL

lishixin/Article/program/Oracle/201311/11198

How to write paging query for oracle and mysql?

Can be written according to the following operations.

1.#Return the first 5 rows Mssql2000 paging using the top keyword (20005 and above versions also support the keyword Selecttop10*fromt_orderwhereidnotin(selectidfromt_orderwhereid>5).

2. Oracle paging using the ROWNUM keyword (three levels of nesting) SELECT*FROM(SELECTA.*,ROWNUMnumFROM(SELECT*FROMt_order)AWHEREROWNUM<=15)WHEREnum>=5;–Return to the 5th -15th row of data.

3. The use of row_number parsing function for paging (more efficient) SELECTxx.*FROM — return to the 5-15 lines of data parsing function can be used in the format of the function over (propertyby field orderby field).

4. Pertion according to a field partitionOrder according to le field sorting.

Beginning oracle, how to write an oracle paging sql statement?

This kind of sql can be found a lot online, to give you an example: x0aselect*from(selecte.*,rownumrnfrom(select*fromemp)e)wherern>=5andrn<=15;x0a where rn is used to specify the row number. The latter, 5 and 15, can be passed in as inputs from the foreground to specify the range of the query.x0ax0a Hope this helps.