Volume El page

The surface is collected in two articles about using ROWNUM to implement pagination for El:
We recommend that you directly read the second article, which is written by Tom of Oracle.
The first article is a reference

Article 1:

Author: chen_liang

For rownum, it is the number of the row returned from the query that is sequentially allocated by the oracle system. The first row is allocated 1, and the second row is 2, this pseudo field can be used to limit the total number of rows returned by the query, and rownum cannot be prefixed with any table name.

(1) rownum query conditions equal to a certain value (For details, refer to article 2)
If you want to find the information of the first student in the student table, you can use rownum = 1 as the condition. However, if you want to find the second student information in the student table, you cannot find the data using rownum = 2. Because rownum starts from 1, but the natural numbers above 1 are regarded as false when rownum is equal to or equal to the true value. Therefore, rownum = n (The Natural Number of n> 1) cannot be found ).

SQL> select rownum, id, name from student where rownum = 1;

(It can be used to limit the number of returned records to avoid errors, for example, implicit cursor)

SQL> select rownum, id, name from student where rownum = 1;
ROWNUM ID NAME
-------------------------------------------------------------------
1 200001 Zhang Yi
SQL> select rownum, id, name from student where rownum = 2;
ROWNUM ID NAME
-------------------------------------------------------------------

(2) rownum for query conditions greater than a certain value (see article 2 for specific explanations)

If you want to find the record from the second row, when rownum> 2 is used, the record cannot be found because rownum is a pseudo column always starting from 1, oracle considers that the condition rownum> n (Natural Number of n> 1) is still not true, so records cannot be found.

SQL> select rownum, id, name from student where rownum> 2;
ROWNUM ID NAME

Then how can we find the record after the second row. You can use the following subquery method to solve the problem. Note that the rownum in the subquery must have an alias; otherwise, the record is not found because rownum is not a column of a table. If the alias cannot be found, you cannot know whether rownum is a subquery column or a primary query column.

SQL> select * from (select rownum no, id, name from student) where no> 2;

NO ID NAME
-------------------------------------------------------------------
3 200003 Li San
4 200004 Zhao Si

SQL> select * from (select rownum, id, name from student) where rownum> 2;
ROWNUM ID NAME
-------------------------------------------------------------------

(3) rownum for query conditions smaller than a certain value

If you want to find the previous record of the third record, use rownum <3 to get two records. Obviously, rownum considers the condition of rownum <n (Natural Number of n> 1) as true, so records can be found.

SQL> select rownum, id, name from student where rownum <3;
ROWNUM ID NAME
-------------------------------------------------------------------
1 200001 Zhang Yi
2 200002 Wang 'er

To sum up the preceding situations, you may need to query rownum data in a certain range. What should you do? We can see that the rownum query condition for a value smaller than a certain value is true, rownum is regarded as false for query conditions greater than a certain value, but it can be converted to true indirectly. Subquery is required. For example, to query the data of rownum between the second row and the third row, including the data of the second row and the third row, we can only write the following statement to first let it return the record rows smaller than or equal to three, then, in the primary query, it is judged that the alias column of the new rownum is greater than or equal to two record rows. However, such operations will affect the speed in the big data set.

SQL> select * from (select rownum no, id, name from student where rownum <= 3) where no> = 2;
NO ID NAME
-------------------------------------------------------------------
2 200002 Wang 'er
3 200003 Li San

(4) rownum and sorting
In Oracle, rownum is the sequence number generated when data is retrieved. Therefore, you must pay attention to the specified rowmun rows of data to be sorted.

SQL> select rownum, id, name from student order by name;
ROWNUM ID NAME
-------------------------------------------------------------------
3 200003 Li San
2 200002 Wang 'er
1 200001 Zhang Yi
4 200004 Zhao Si

It can be seen that rownum is not the serial number generated by the name column. The system assigns the number of the record row according to the sequence in which the record is inserted, and the rowid is also allocated sequentially. To solve this problem, you must use the subquery

SQL> select rownum, id, name from (select * from student order by name );
ROWNUM ID NAME
-------------------------------------------------------------------
1 200003 Li San
2 200002 Wang 'er
3 200001 Zhang Yi
4 200004 Zhao Si

In this way, the sequence is sorted by name, and the correct sequence number (from small to large) is marked with rownum)

 

I have a table with millions of records in my work. To display the table by page on the jsp page, we will consider using rownum. The following is a specific method (each page
20 ):

Select * from tabname where rownum <20 order by name

However, it is found that oracle cannot be executed as per its own needs. Instead, it can be executed randomly.
Take 20 records, and then order by. After consulting oracle, it is said that rownum is indeed like this. If you want to use it, you can use subqueries to sort the records first and then rownum. The method is as follows:

Select * from (select * from tabname order by name) where rownum <20, but the efficiency is much lower.
After the author's experiment, we only need to add a primary key or index to the order by field to let oracle first sort by this field, and then rownum; the method remains unchanged:
"Select * from tabname where rownum <20 order by name"

Obtains the nth row in a column.

Select column_name from
(Select table_name. *, dense_rank () over (order by column desc) rank from table_name)
Where rank = & N;

If you want to return the first five records:
Select * from tablename where rownum <6; (or rownum <= 5 or rownum! = 6)

If you want to return records 5-9:

Select * from tablename
Where...
And rownum <10
Minus
Select * from tablename
Where...
And rownum <5
Order by name

After the results are selected, the results are sorted by name. (Select first and then sort)

Note: Only the above symbols can be used (<, <= ,! = ).

Select * from tablename where rownum! = 10; the first nine records are returned.
Cannot be used:>,> =, =, Between... and. Since rownum is a pseudo column that always starts from 1, Oracle considers this condition invalid and cannot find the record.

In addition, this method is faster:

Select * from (
Select rownum r, a from yourtable
Where rownum <= 20
Order by name)
Where r> 10
In this way, the 11-20 records are retrieved! (Select, sort, and then select)

To sort and then select, select Nesting is required: the outer layer of the inner layer is selected.
Rownum is generated with the result set. Once generated, it will not change. At the same time, the generated results are incremental in sequence, and there will never be 2 without 1!
Rownum is a pseudo column generated when the query set is generated. If the where condition contains the rownum condition, then:

1: If the condition is a constant, then:
Only rownum = 1, <= a natural number greater than 1, = A number greater than 1 has no results, and a number greater than one has no results.
That is, when a rownum does not meet the condition, the query ends. this is stop key!

2: when the judgment value is not a constant
If the condition is = var, the condition is met only when var is 1. In this case, the stop key does not exist. full scan is required to determine the data that meets other where conditions.
Select a row before selecting rows with rownum = 2 ......

Supplement:
Article 2:

Today, we found Orcale's paging description:
Http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

The following is a paging SQL statement that can work normally:

 

 

A is the alias of the innermost query result.

The following is a very important section, which is very clear. It is recommended that you take a closer look to understand how database queries work:

How ROWNUM Works

ROWNUM is a pseudo column (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4 ,... n, where N is the number of rows in the set ROWNUM is used. a rownum value is not assigned permanently to a row (this is a common misconception ). A row in a table does not have a number; you cannot ask for row 5 from a table-there is no such thing.

! When is ROWNUM assigned to each record!
Also confusing to assign people is when a ROWNUM value is actually assigned. a rownum value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

Select *
From t
Where ROWNUM> 1; Because ROWNUM> 1 is not true for the first row, ROWNUM does not advance to 2. hence, no ROWNUM value ever gets to be greater than 1. consider a query with this structure:

Select..., ROWNUM
From t
Where <where clause>
Group by <columns>
Having Order by <columns>; Think of it as being processed in this order:

1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. group by is applied.
5. HAVING is applied.
6. order by is applied.

That is why a query in the following form is almost certainly an error:

Select *
From emp
Where ROWNUM <= 5
Order by sal desc; The intention was most likely to get the five highest-paid people-a top-N query. what the query will return is five random records (the first five the query happens to hit), sorted by salary. the procedural pseudo docode for this query is as follows:

ROWNUM = 1
For x in
(Select * from emp)
Loop
Exit when NOT (ROWNUM <= 5)
OUTPUT record to temp
ROWNUM = ROWNUM + 1
End loop
SORT TEMPIt gets the first five records and then sorts them. A query with where rownum = 5 or where rownum> 5 doesn't make sense. this is because a ROWNUM value is assigned to a row during the predicate evaluation and gets incremented only after a row passes the WHERE clause.

Here is the correct version of this query:

Select *
From
(Select *
From emp
Order by sal desc)
Where ROWNUM <= 5; This version will sort EMP by salary descending and then return the first five records it encounters (the top-five records ). as you'll see in the top-N discussion coming up shortly, oracle Database doesn't really sort the entire result set-it is smarter than that-but conceptually that is what takes place.

 

Description:
1. rownum is the number of the row returned from the query in the oracle system. The first row is allocated 1 and the second row is 2,
This pseudo field can be used to limit the total number of rows returned by the query.
2. rownum cannot be prefixed with any base table name.

3. in sorting, because oracle extracts records before sorting, and oracle's rownum is generated after extracting records, it takes precedence over sorting operations. Therefore, you must use subqueries to sort records first.

Use the following methods:

GL_CAT_SUM is the table I referenced.

// Return the specified row record

SQL> select * from (select rownum a, t. * from GL_CAT_SUM t) where a = 2

// Sort by pseudo Columns

SQL> select * from (select rownum a, t. * from GL_CAT_SUM t) order by

// Returns records with a pseudo column greater than or equal to 1 and less than or equal to 4.

SQL> select * from (select rownum a, t. * from GL_CAT_SUM t where rownum <= 4) where a> = 1

// Return the last record

SQL> select * from (select rownum a, t. * from GL_CAT_SUM t) where a = (select count (*) from GL_CAT_SUM)

// Return the records of the last row minus N rows. For example, if the rownum of the last row is 5 and N is 1, the SQL statement returns the records of rownum = 4.

SQL> select * from (select rownum a, t. * from GL_CAT_SUM t) where a = (select count (*)-N from GL_CAT_SUM)

 

The above is my work on the Internet ..

Paging query is one of the many tips used in web development. There are also many implementation methods. A good way for Oracle databases is to use the ROWNUM pseudo column.

 

Select * from (

Select rownum ro, ordersid

From orders

Where rownum <= 100

)

Where ro> 80

 

The data obtained in this way is 20 rows in the result set where ROWNUM is about 80 and less than 100, which improves the query speed by page.

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/paulin/archive/2008/04/08/2260357.aspx