Using "Go" for SQL optimization __rownum

the concept of rownum
RowNum is a false column. It will be assigned as 1,2,3,4, ... N,n is the number of rows. A rownum value is not permanently assigned to a row (which is most easily misunderstood). A row in the table does not have a label; you cannot query a row with a value of 5 rownum-there is no such concept at all. Another confusing question is when the rownum value is assigned. The allocation of the RowNum value is done before any sort and aggregation after the predicate of the query is parsed. RowNum values grow only when they are allocated. This is why the following query will never return a result:
SELECT * from countries where rownum>1;
ROWNUM > 1 is not a true value for the first line, ROWNUM does not grow to 2. Therefore, there is no greater than the 1 ROWNUM. Never use ' rownum> ' and ' rownum=2 ... N ' such a condition.

show the top N records of a sort result with rownum
The From/where clause is executed first.
Based on the rows that are output by the From/where clause, RowNum are assigned to them and grow from them.
SELECT is applied.
GROUP by IS applied.
Have is being applied.
ORDER by IS applied.
That's why the following SQL is almost always wrong:
Select Rownum,job_title,min_salary
From the jobs where rownum<3 order by min_salary;
The correct wording:
Select rownum,tmp.* from (
Select Job_title,min_salary
From-Jobs order by Min_salary) TMP
where rownum<=3;

using rownum for paging
SELECT * FROM
(Select/*+ first_rows (n) */a.*,
ROWNUM Rnum
From (Your_query_goes_here, with order by) a
where ROWNUM <=
: Max_row_to_fetch)
Where Rnum >=: Min_row_to_fetch;
First_rows (n) allows the optimizer to consider the shortest time to obtain the first N records.
: Max_row_to_fetch the last row of a result set on a page. If you display 10 rows per page and you want to display page 6th, this value is 60.
: Min_row_to_fetch The first row of a result set on a page. If you display 10 rows per page and you want to display page 6th, this value is 50.

The effect of rownum on performance
RowNum can prevent Oracle from sorting on disk. RowNum cannot avoid a full table scan, but it avoids sorting the entire table data, and the sort operation can be done in memory easily after specifying rownum.

Using "Go" for SQL optimization __rownum