Convert: rowid and rownum

Two useful items for querying by page.

Rowid and rownum are rarely used by database developers, because most of them are for data batch processing in enterprise database development, but they will still be used by other database developers.
Rowid and rownum are both virtual columns, but they have different meanings. Rowid is a physical address used to locate the physical storage location of specific data in Oracle, while rownum is the sorting of SQL output results. In general, rowid remains unchanged, and rownum changes, especially when order by is used.

Rowid:
1. Why is rowid used?
Oracle uses rowid as the unique identifier of the B-tree and its internal algorithm to mark the row. In
In versions earlier than oracle8, rowid indicates file, block, and row.
Number. Only one digit represents the file number. In oracle8, a datafile has two numbers:
An absolute value is unique throughout the database. You can see the file_id in dba_data_files.
A relative value is unique in tablespace. You can see relative_fno in dba_data_files.

The new rowid uses relative values. Therefore, you must store the ID of the segment. Otherwise, it will be confused. Therefore, oracle8 adds the object's segment number to rowid to indicate table or partition.

2. rowid Structure
Use base-64 code, including a-Z, A-Z, 0-9, + ,-. A total of 18 digits. 1-6 bits: representing the 7-9 bits of the object. The relative value of the object is 10-15: Block 16-18 in the file: slot value in the block

3. tablespace-relative addressing
Tablespace-relative addressing is used. Multiple files can have the same
Because they belong to different tablespaces, the absolute address cannot be obtained from the new rowid, but this is no problem.
When processing an object, you can determine which tablesapce it belongs. In tablespace, the object relative value is unique, so rowid also
Is a unique object. Tablespace-relative addressing is a key technology in oracle8 that supports ultra-large databases.

4. Data Object number
The data object number indicates the segment (partial/fragment). All segments have data object numbers, which are stored in each data block (Block/area) and are not repeated.

At first, dba_objects.object_id = DBA_OBJECTS.DATA-OBJECT_ID, but in the above case
The DATA-OBJECT_ID will add the truncate table move partition as follows
Oracle checks the data object number in the rowid and the data object in the block.
Number to ensure that the versions are consistent. Oracle also uses data object
Number to ensure that the rollback record is consistent with the latest segment record. Note that the data object number is not an object.
Mark

5. Restricted rowid
The rowid format of oracle7 is 1-8 bits: block number
9-12 bits: row number 13-16 bits: file number oracle8 supports short and old format rowid.
Index Entry of nopartition Table Local index entry of Partition Table
Row piece chain pointer limited the internal storage of rowid is 6 bytes, 4 bytes = data block
Number 2 byte = row number, that is, Index
The entry uses 6 bytes to store the rowid, which is sufficient for most indexes. However, this short rowid cannot be used in the global
Index, because partition may span across tablespace. It shows that this rowid is still 18 bits.

6. Extended rowid
Oracle stores 10 bytes internally, including (data object number, data block
Nu mber, row number) oracle8 uses the extended rowid: Partition Table's Global
The extended rowid of the Index Server algorithm is still 18 characters displayed in the SELECT statement and stored in the rowid field.

7. Use the rowid of oracle7 in oracle8
When you query the rowid of oracle7 from the database of oracle8
It is an oracle7 format and can also be used in the where statement.
When querying the rowid of oracle8 from oracle7 dB, rowid returns the format of oracle8, which can also be used in the where statement, but cannot be stored
In the rowid field. However, you must use the dbms_rowid package to explain it. If the extended oracle8
Rowid, which cannot import oracle8 data to oracle7. You can import data from oracle7 to oracle8.

8. Application migration problems
Generally, there should be no problem with program porting. The porting problem is considered only in the following situations:
The application uses the rowid table field including the rowid type. If the program has the following conditions, you must use the dbms_rowid package:
You can combine rowids and separate them by yourself. If you only pass rowid to a variable or use it as a whole, it is not affected.

9. data migration problems
The rowid field in oracle7 is automatically expanded to oracle8 regardless of whether export/import is used or the port migration tool. If a field contains rowid, you must manually use the dbms_rowid package for conversion.

10. dbms_rowid package
Created by $ ORACLE_HOME/rdbms/admin/dbmsutil. SQL
Catproc. SQL contains. Provides some functions for processing rowid. Rowid_create rowid_info rowid_type
Rowid_object rowid_relative_fno rowid_block_number
Rowid_to_absolute_fno 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 from rowid to extended rowid, which is used to convert the old rowid to oracle8 format.

Dbms_rowid.rowid_to_restricted converts the extended rowid 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 explain rowid. Data Object number, relative file number, block number, and row number can be obtained.

Dbms_rowid.create generates rowid.

Rownum:

In Oracle, to query the first N records according to specific conditions, use rownum. Select * from EMP
Whererownum <= 5 and the book also warns you not to use ">" for rownum, which means that if you want to use select * from
EMP whererownum> 5 fails. You need to know why the failure occurs.

The mechanism behind rownum:
1 Oracle executes your query.

2 Oracle fetches the first row and callit 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.

After understanding the principle, we can see that rownum> will not succeed, because the row queried in step 3 has been discarded, and the rownum found in Step 4 is still 1, which will never succeed.

Similarly, if rownum is used separately with =, it is only useful when rownum = 1.

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. Therefore, this pseudo field can be
Limit the total number of rows returned by the query, and rownum cannot be prefixed with any table name. Example: Table: Student (student) table. The table structure is as follows:
Id char (6) -- Student ID name varchar2 (10) -- name CREATE TABLE
Student (ID char (6), name varchar2 (100); insert into sale
Values ('000000', 'zhang yi'); insert into sale values ('000000', 'wang 2'); insert
Into sale values ('000000', 'Lee 3'); insert into sale values ('000000', 'zhao si ');
Commit; (1)
Rownum: If you want to find the information of the first student in the student table that is equal to a certain value, you can use rownum = 1 as the condition. However, if you want to find the second student information in the student table
Data cannot be found 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 result, it cannot be queried.
To rownum = N (Natural Number of n> 1 ). SQL> selectrownum, ID, name from student
Whererownum = 1; (it can be used to limit the number of returned records to avoid errors, such as implicit cursor) SQL>
Selectrownum, ID, name from student whererownum = 1; rownumid name
----------------
--------------------------------------------------- 1 200001 Zhang Yi
SQL> selectrownum, ID, name from student whererownum = 2;
Rownumid name ----------------
---------------------------------------------------
(2) rownum for query conditions greater than a certain value
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.
The condition rownum> N (Natural Number of n> 1) is still not true, so the SQL> selectrownum, ID, name
From student whererownum> 2; rownumid 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. This is because
Rownum is not a column in a table. If you cannot afford an alias, you cannot know whether rownum is a subquery column or a primary query column. SQL> select *
From (selectrownumno, ID, name from student) where no> 2; no
ID name ----------------
--------------------------------------------------- 3 200003
Li San 4 200004 Zhao Si SQL> select * from (selectrownum, ID, name from
Student) whererownum> 2; rownumid name ----------------
---------------------------------------------------
(3) rownum for query conditions smaller than a certain value if you want to find the record before the third record, when using rownum <3, you can get two records. Apparently rownum
The condition of rownum <n (Natural Number of n> 1) is considered to be true, so records can be found. SQL> selectrownum, ID, name
From student whererownum <3; rownumid name ----------------
--------------------------------------------------- 1 200001 Zhang Yi 2
200002
In summary, Wang Er may need to query the rownum data in a certain range. What should I do? We can see that the rownum query condition for a value smaller than a certain value is true.
Rownum is regarded as false directly for query conditions greater than a certain value, but it can be converted to true indirectly. Subquery is required. For example, to query rownum
For data between the second row and the third row, including the second row and the third row, we can only write the following statement to first let it return record rows smaller than or equal to three, then, the new rownum is determined in the primary query.
Record rows whose names are greater than or equal to two. However, such operations will affect the speed in the big data set. SQL> select * from
(Selectrownumno, ID, name from student whererownum <= 3) where no
> = 2; no ID name ----------------
--------------------------------------------------- 2 200002
Wang 2 3 200003 Li 3 (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>
Selectrownum, ID, name from student order by name; rownumid name
----------------
--------------------------------------------------- 3 200003
Li San 2 200002 Wang 'er 1 200001 Zhang Yi 4 200004
Zhao Si can see 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> selectrownum, ID, name from (select * from student order
By name); rownumid name ----------------
--------------------------------------------------- 1 200003
Li San 2 200002 Wang 'er 3 200001 Zhang Yi 4 200004
Zhao Si sorted by name and marked the correct sequence number with rownum (small to large)