pagination display and optimization of data in Oracle

In a page is often the phenomenon of paging, then the background database should be how to page display it?

Paging can use the RowNum function in Oracle.

The display of data from the first line to the first row of a page.

Below is a description of the EMP table under the Scott User:

Statement one:

Select b.*
From
Select A.*, RowNum row_num
From (SELECT * from EMP) a) b
where row_num between 3 and 5;

Statement two:

Select b.*
From
Select A.*, RowNum row_num
From (SELECT * from EMP) a
where rownum<6) b
where row_num>2;

Statement one and statement two have the same function, can be the EMP table in the third row to the fifth row of data display, but when the data in the database is large, statement two is significantly more optimized than the statement one.

In Oracle, the execution of SQL statements is executed from the inside out, in which the rownum<6 data is first put into table B, and then the ROWNUM>2 data is queried from table B, which is more efficient than statement I.

pagination display and optimization of data in Oracle