The difference and usage of rownum and ROWID in Oracle __oracle

Turn from: http://tenn.iteye.com/blog/99339

In Oracle, to query the top N records on a specific condition, use a rownum.
SELECT * from emp where rownum <= 5
And the book also warns that you can't use ">" for rownum, which means that if you want to use
SELECT * from emp where rownum > 5
is a failure. To know why you fail, you need to understand the mechanism behind rownum:
1 Oracle executes your query.

2 Oracle fetches the "I" and calls it row number 1.

3 Have We gotten past row number meets the criteria? If no, then Oracle discards the row, if yes, then Oracle return the row.

4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

5 Go to step 3.

Understand the principle, you know rownum> will not succeed, because in the third step when the query out of the line has been discarded, the fourth step to find out the rownum is still 1, so that will never succeed.

By the same token, if rownum alone =, it is only useful when rownum=1.

For RowNum, it is an Oracle system sequence that is assigned the number of rows returned from the query, the first row is assigned 1, the second row is 2, and so on, this pseudo field can be used to limit the number of rows returned by the query, and rownum cannot be prefixed with the name of any table.
An example is provided:
For example, table: Student (Student) table, table structure is:
ID Char (6)--School number
Name VARCHAR2 (10)--Name
CREATE TABLE Student (ID char (6), name VARCHAR2 (100));
INSERT into sale values (' 200001 ', ' Zhang Yi ');
INSERT into sale values (' 200002 ', ' King II ');
INSERT into sale values (' 200003 ', ' lie triple Systems ');
INSERT into sale values (' 200004 ', ' Zhao Si ');
Commit
(1) rownum for the query condition equal to a value
If you want to find information about the first student in the student table, you can use Rownum=1 as a condition. But to find the second student in the student table, the data is not available using the rownum=2 results. Since rownum are all starting from 1, but more than 1 of the natural numbers in rownum do equal to the judgment is considered false condition, so can not find rownum = N (n>1 natural number).
Sql> Select Rownum,id,name from student where rownum=1; (You can use a limit to return the number of records to ensure no errors, such as: implicit cursors)
Sql> Select Rownum,id,name from student where rownum=1;
RowNum ID NAME
---------- ------ ---------------------------------------------------
1 200001 Sheets A
Sql> Select Rownum,id,name from student where rownum = 2;
RowNum ID NAME
---------- ------ ---------------------------------------------------
(2) RowNum for query conditions greater than a value
If you want to find a record from the second row, when you use ROWNUM&GT;2, you cannot find the record because rownum is a pseudo column that always starts with 1, and Oracle thinks rownum> N (the natural number of n>1) is still not tenable, So I can't find the records.
Sql> Select Rownum,id,name from student where RowNum >2;
RowNum ID NAME
---------- ------ ---------------------------------------------------
How can we find the record after the second line? You can use the following subquery methods to resolve. Note that the rownum in the subquery must have an alias, or the record will not be detected, because rownum is not a column of a table, and it is not possible to know whether RowNum is a column of a subquery or a column of the main query without an alias.
Sql>select * FROM (select RowNum No, id,name from student) where no>2;
NO ID NAME
---------- ------ ---------------------------------------------------
3 200003 Lie triple systems
4 200004 Zhao Si
Sql> SELECT * FROM (select Rownum,id,name from student) where rownum>2;
RowNum ID NAME
---------- ------ ---------------------------------------------------
(3) RowNum for a query condition that is less than a value
If you want to find the previous record of the third record, you can get two records when using Rownum<3. Obviously rownum for rownum<n (the natural number of n>1) is considered to be tenable, so you can find the record.
Sql> Select Rownum,id,name from student where RowNum <3;
RowNum ID NAME
---------- ------ ---------------------------------------------------
1 200001 Sheets A
2 200002 King Two
In a few cases, may sometimes need to query rownum in a certain interval of data, then how to do it from the above can be seen rownum to less than a value of the query condition is artificial true, rownum for more than a value of the query conditions are directly considered false, But it can be indirectly turned into the view that it is true. Then you must use subqueries. For example, to query rownum between the second and third rows of data, including the second and third rows of data, we can only write the following statement, let it return a row of records less than or equal to three, and then in the main query to determine the new rownum is greater than or equal to two of the record row. But such operations can affect speed in large data sets.
Sql> SELECT * FROM (select RowNum no,id,name from student where rownum<=3) where no >=2;
NO ID NAME
---------- ------ ---------------------------------------------------
2 200002 King Two
3 200003 Lie triple systems
(4) RowNum and sorting
The rownum in Oracle is the sequence number that is produced when the data is fetched, so it is important to be aware of the rowmun rows of data that you want to specify for the sorted data.
Sql> Select RowNum, id,name from student order by name;
RowNum ID NAME
---------- ------ ---------------------------------------------------
3 200003 Lie triple systems
2 200002 King Two
1 200001 Sheets A
4 200004 Zhao Si
As you can see, rownum is not the serial number generated by the Name column. The system is the number of records in the order in which they were inserted, and ROWID is also assigned sequentially. To solve this problem, you must use a subquery
Sql> Select RowNum, Id,name from (SELECT * to student order by name);
RowNum ID NAME
---------- ------ ---------------------------------------------------
1 200003 Lie triple systems
2 200002 King Two
3 200001 Sheets A
4 200004 Zhao Si
This is sorted by name, and the correct ordinal number is marked with rownum (small to large)

Like RowNum, Oracle also offers another pseudo sequence: rowID. However rowID and rownum are different, generally the corresponding rowid of each row of data is fixed and unique, when this line of data is stored in the database is determined. rowID can be used to query records, and through the ROWID query record is the fastest query method. (I haven't tried this, in addition, it is difficult to remember a string with a length of 18 bits, and there is no obvious rule, so I personally think that using ROWID query record is not very practical rowid only if the table is moved (such as table space changes, data import/export), Will change.