The difference between ROWID and rownum

rowID and rownum are rarely used by database developers, because most of the data is batched in enterprise database development, but it is still used by other database personnel.
rowID and RowNum are virtual columns, but their meanings are completely different. rowID is the physical address that locates the physical storage location of the specific data in Oracle, while RowNum is the order of the output results of SQL. In layman's terms: ROWID is relatively constant, and rownum will change, especially when using order by.

The location of the rowID used to locate a data in a datasheet is unique and does not change

rownum means to query the location of a record in the entire result set, the same record query condition different corresponding rownum is different and rowid will not change

For example, like the next table: USER

Name	Age
Tom	20
John doe	22
Harry	23

When executing a query: Select rowID, RowNum, name,age from USER order by age ASC , the results are as follows:

rowID	RowNum	Name	Age
Aaaowhaaqaaalgdaaa	1	Tom	20
Aaaowhaataaalp7aad	2	John doe	22
Aaaowhaataaalp7aae	3	Harry	23

When executing a query: Select rowID, RowNum, name,age from the USER order by age desc The results are as follows:

rowID	RowNum	Name	Age
Aaaowhaataaalp7aae	1	Harry	23
Aaaowhaataaalp7aad	2	John doe	22
Aaaowhaaqaaalgdaaa	3	Tom	21st

ROWID:
1. Why Use rowID 　　

Oracle rowID as a B-tree and its internal algorithm to mark the only indication of the row. In previous versions of ORACLE8, rowID marked file, Block,row number, with only one digit representing the file number.

In ORACLE8, a datafile is represented by two digits:

1.) An absolute value that is unique to the entire database. You can see the file_id in the Dba_data_files.

Sql> SELECT file_id from Dba_data_files; FILE_ID----------4 3 2 1

2.) A relative value, which is unique in tablespace, can be seen in the dba_data_files of RELATIVE_FNO.

Sql> SELECT relative_fno from Dba_data_files; RELATIVE_FNO------------4 3 2 1
The new ROWID uses relative values, so the segment must be stored, otherwise it will be confusing. So ORACLE8 add the object's segment number to the ROWID to mark the table or partition.

2, the structure of ROWID

Use base-64 code, including a-z,a-z,0-9,+,-。 　　Altogether 18 bits. 1-6-bit: Representing object 7-9-bit: File relative value 10-15: Slot value in block 16-18:block in file

3, tablespace-relative addressing mode uses tablespace-relative addressing mode, multiple files can have the same relative value, because they belong to different tablespace, so can not be the new ROWID get an absolute address, But that's fine, because when you're dealing with an object, you can already determine which tablesapce it belongs to. In the tables pace, the file relative value is unique, so ROWID can still uniquely mark an object. TABLE Space-relative Addressing method is the key technology in ORACLE8 to support super large database.

4, data Object Number data object number is used to indicate segment, all segment have data object number, stored in each data block, and do not repeat.

At the very beginning, dba_objects. Object_id=dba_objects. DATA-OBJECT_ID, but in this case data-object_id will increase the TRUNCATE TABLE move in the following cases PARTITION Oracle will check the data OBJECT number and ROWID in Blo 　　The data OBJECT number in CK ensures that the version is consistent between them. 　　Oracle also uses data OBJECT number to ensure that rollback records are consistent with the latest segment records. Note that data object number is not a sign of object

5. The ROWID format of the restricted ROWID ORACLE7 is 1-8 bits: Block number 9-12: ROW number 13-16 bit: FILE number ORACLE8 support short, old format ROWID, 　　The effect is on the index ENTRY of the Nopartition table to the partition table's local index ENTRY ROW Piece CHain The internal storage of the restricted pointer is 6BYTE, 4byte=data Block number 2byte=row number means that the index entry uses 6BYTE to store the ROWID, which is sufficient for most index. However, this short rowid cannot be used on the global index of Patition table because partition may cross tablespace. Shows that this rowid is still 18-bit.

6. Extended rowID Oracle is in BYTE at internal storage, including (DATA OBJECT number,data block NU mber,row number) ORACLE8 using extended rowid:partition The rowid of the global INDEX SERVER algorithm extension for TABLE is still displayed in the 18-bit display at select time, and is stored in the rowID field.

7, in the ORACLE8 use of ORACLE7 rowid from ORACLE8 db query ORACLE7 rowID, ROWID return is the format of ORACLE7, can also be used in the where statement. When querying ORACLE8 's rowid from ORACLE7 DB, ROWID returns the ORACLE8 format, or it can be used in the where statement, but not in the rowID field. 　　But you have to use the Dbms_rowid package to explain it. If you include an extended ORACLE8 ROWID, this does not import the ORACLE8 data into ORACLE7. From the ORACLE7 can be import into the ORACLE8.

8, the application transplant problem general procedure transplant should have no problem. The migration issue is considered only under the following conditions: Application uses ROWID table including ROWID type fields if the program has the following conditions, you must use the DBMS_ROWID package: own combination of ROWID decomposition rowID if only to pass the row IDs to variables, or simply as a whole, can be unaffected.

9, the data migration problem regardless of using export/import also use the Migration Tool, ORACLE7 in the rowID field in the ORACLE8 automatically expands. If you include rowid in a field's content, you must manually convert it using the Dbms_rowid package.

10, Dbms_rowid package created by $oracle_home/rdbms/admin/dbmsutil.sql, in fact, contained in the Catproc.sql. 　　Provides some functions to handle rowid. 　　Rowid_create rowid_info rowid_type rowid_object rowid_relative_fno rowid_block_number rowid_to_extended rowid_to_restricted rowid_verify

Dbms_rowid. 　　Rowid_to_extended (Old_rowid in ROWID, schema_name in Varchar2, object_name in Varchar2, conversion_type in number 　　) return ROWID; The conversion is restricted ROWID to the extended rowid, which is used to convert the old rowid to ORACLE8 format.

Dbms_rowid. Rowid_to_restricted transforms the ROWID of the extension to the restricted ROWID.

Dbms_rowid. Rowid_verify determines whether a restricted ROWID can be converted to an extended format

Dbms_rowid. Row_info is used to interpret the ROWID, you can get the data OBJECT number,relative FILE number,block number and row number.

Dbms_rowid. Create generates ROWID.

RowNum:

In Oracle, to query the top N records on a specific condition, use a rownum. SELECT * FROM EMP whererownum<= 5 and the book cautions that ">" cannot be used against rownum, which means that if you want to use the SELECT * from EMP whererownum> 5 it will fail. To know why it fails, 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.   Examples: For example, table: Student (Student) table, table structure: ID     char (6)--School Number name VARCHAR2 (10)--Name CREATE TABLE stud ENT (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 number in rownum is equal to the judgment is considered false condition, so can not find rownum= N (n>1 natural number). Sql> Selectrownum,id,name from Student whererownum=1 (can be used to limit returns to the number of record bars, ensure no errors, such as: implicit cursors) sql> Selectrownum,id,name From student whererownum=1;    rownumid     NAME------------------------------- ------------------------------------          1 200001 Zhang Sql> Selectrownum,id,name from Student whererownum=2;    rownumid     NAME-------------------------------------------------------------- -----(2) rownum for query conditions greater than a value     if you want to find records that are recorded from the second row, when you use ROWNUM&GT;2, you cannot find the record because rownum is a pseudo column that always starts at 1, Oracle considers rownum> N (the natural number of n>1) This condition is still not valid, so the records sql> Selectrownum,id,name from student whererownum>2; rownumid     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 (Selectrownumno, id,name from student) where no>2;          NO id     NAME-------------------------------------------------------------------           3 200003 lie triple systems            4 200004 Zhao four sql> select * FROM (selectrownum,id,name from student) Whererownum>2;    rownumid     NAME------------------------------------------------------------------ -(3) RowNum if you want to find a previous record of a third record for a query condition that is less than a value, two records are available 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> selectrownum,id,name from student whererownum<3;    rownumid     name- ------------------------------------------------------------------1 200001 Sheets           2 200002 Wang Two comprehensive in several 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 the query condition that is greater than a value is directly considered false, but it can be indirectly converted to 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 (selectrownumno,id,name from student whererownum<=3) where no &GT;=2;&NBSP;&NBSP;&NBSP;&NBSP;&N bsp;    NO id     NAME-------------------------------------------------------- -----------          2 200002 King two           3 200003 Lie triple Systems (4) rownum and sort the rownum in Oracle are the numbers that are produced when the data is fetched, so it is important to be aware of the Rowmun row data that you want to specify for the sorted data. Sql> Selectrownum,id,name from student to name;    rownumid     name------ -------------------------------------------------------------           3 200003 Lie triple systems           2 200002 King two            1 200001 Zhang one           4 200004 Zhao Four can see that RowNum is not a sequence number that is 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 resolve this problem, you must use the subquery sql> selectrownum,id,name from (SELECT * from Student order by name);    rownumid&nbsp ;    NAME-------------------------------------------------------------------           1 200003 lie triple systems           2 200002 King two           3 200001 Zhang one            4 200004 Zhao four that's it. Sort by name and rownum the correct ordinal number (small to large)