Shulou(Shulou.com)06/01 Report--

Rowid and Rownum are rarely used by database developers because batch data is mostly used in enterprise database development, but it is still used by other database staff.

Both rowid and rownum are imaginary columns, but they have completely different meanings. Rowid is the physical address that is used to locate the physical storage location of specific data in Oracle, while rownum is the sort of output of sql. Popularly speaking: rowid is relatively constant, rownum will change, especially when using order by.

Rowid is used to locate a piece of data in a data table, which is unique and will not be changed.

Rownum means to query the location of a record in the entire result set. The rownum corresponding to different query conditions of the same record is different, but the rowid will not change.

For example, there is the following table: USER

Name

Age

Zhang San

twenty

Li Si

twenty-two

Wang Wu

twenty-three

When executing the query: select rowid,rownum,name,age from USER order by age asc, the result is as follows:

Rowid

Rownum

Name

Age

AAAOWhAAQAAALgdAAa

one

Zhang San

twenty

AAAOWhAATAAALp7AAd

two

Li Si

twenty-two

AAAOWhAATAAALp7AAe

three

Wang Wu

twenty-three

When executing the query: select rowid,rownum,name,age from USER order by age desc, the result is as follows:

Rowid

Rownum

Name

Age

AAAOWhAATAAALp7AAe

one

Wang Wu

twenty-three

AAAOWhAATAAALp7AAd

two

Li Si

twenty-two

AAAOWhAAQAAALgdAAa

three

Zhang San

twenty-one

ROWID:

1. Why use ROWID

ORACLE takes ROWID as the only sign of ROW marked by B-tree and its internal algorithm. In previous versions of ORACLE8, ROWID marked FILE and BLOCK,ROW NUMBER, and only one number represented the FILE number.

In ORACLE8, a DATAFILE has two numbers:

1.) An absolute value that is unique to the entire database. You can look at the FILE_ID in DBA_DATA_FILES.

[java] view plain copy

SQL > SELECT FILE_ID FROM DBA_DATA_FILES

FILE_ID

-

four

three

two

one

2.) A relative value, which is unique in TABLESPACE, can be seen in RELATIVE_FNO in DBA_DATA_FILES.

[java] view plain copy

SQL > SELECT RELATIVE_FNO FROM DBA_DATA_FILES

RELATIVE_FNO

-

four

three

two

one

The new ROWID uses relative values, so you must store the SEGMENT tag, otherwise it will be confusing. So ORACLE8 adds the SEGMENT number of the object to the ROWID to mark TABLE or PARTITION.

2. The structure of ROWID

Use base-64 code, including a murazine, Amurz, Zpeng, zero, nine, and zero. There are 18 of them. 1-6 bits: represent OBJECT 7-9 bits: file relative value 10-15: slope value in BLOCK 16-18:BLOCK in file

3, TABLESPACE-Relative addressing uses TABLESPACE-Relative addressing, multiple files can have the same relative value, because they belong to different TABLESPACE, so can not get the absolute address from the new ROWID, but this is no problem, because when dealing with an OBJECT, you can already determine which TABLESAPCE it belongs to. In TABLES PACE, the relative value of the file is unique, so the ROWID can still be uniquely marked with an OBJECT. TABLE SPACE-Relative addressing is the key technology to support super-large database in ORACLE8.

4. DATA OBJECT NUMBER DATA OBJECT NUMBER is used to indicate SEGMENT. All SEGMENT have DATA OBJECT NUMBER, which is stored in each DATA BLOCK and is not repeated.

At first, DBA_OBJECTS.OBJECT_ID=DBA_OBJECTS.DATA-OBJECT_ID, but in the above cases DATA-OBJECT_ID will add TRUNCATE TABLE MOVE PARTITION ORACLE in the following cases will check the DATA OBJECT NUMBER in ROWID and the DATA OBJECT NUMBER in BLOCK to make sure that the versions between them are consistent. ORACLE also uses DATA OBJECT NUMBER to ensure that ROLLBACK records are consistent with the latest SEGMENT records. It is important to note that DATA OBJECT NUMBER is not a sign of OBJECT.

5. The ROWID format of RESTRICTED ROWID ORACLE7 is 1-8 bits: BLOCK NUMBER 9-12 bits: ROW NUMBER 13-16 bits: FILE NUMBER ORACLE8 supports short, old format ROWID, the function is to NOPARTITION TABLE INDEX ENTRY to PARTITION TABLE LOCAL INDEX ENTRY ROW Piece CHain pointer restricted ROWID internal storage is 6BYTE, 4BYTE=DATA BLOCK NUMBER 2BYTE=ROW NUMBER, that is to say, INDEX ENTRY uses 6BYTE to store the ROWID This is sufficient for most INDEX. However, this short ROWID cannot be used on PATITION TABLE's GLOBAL INDEX, because PARTITION may span TABLESPACE. It shows that this kind of ROWID is still 18 bits.

6. The extended ROWID ORACLE is 10 BYTE when stored internally, including (DATA OBJECT NUMBER,DATA BLOCK NUMBER, ROW NUMBER) ORACLE8 using the extended ROWID: PARTITION TABLE's GLOBAL INDEX SERVER algorithm to expand the ROWID in SELECT, it is still 18-bit display and stored in the ROWID field.

7. When you use the ROWID of ORACLE7 in ORACLE8 to query the ROWID of ORACLE7 from the DB of ORACLE8, ROWID returns the format of ORACLE7, which can also be used in the WHERE statement. When querying ORACLE8's ROWID from ORACLE7's DB, ROWID returns the format of ORACLE8, which can also be used in WHERE statements, but cannot be stored in the ROWID field. But you have to use the DBMS_ROWID package to explain it. If an extended ORACLE8 ROWID is included, it is not possible to IMPORT the ORACLE8 data into ORACLE7. You can IMPORT from ORACLE7 to ORACLE8.

8. Transplant of APPLICATION there should be no problem with the transplant of general programs. The issue of migration is considered only in the following cases: application uses rowid table, including fields of type ROWID. If the program has the following situations, you must use the DBMS_ROWID package: assemble your own ROWID and decompose your ROWID. If you just pass ROWID to variables, or just use it as a whole, it will not be affected.

9. The problem of data migration no matter using EXPORT/IMPORT or migration tools, the ROWID field in ORACLE7 is automatically extended to ORACLE8. If you include ROWID in the contents of a field, you must convert it manually with the DBMS_ROWID package.

10. The DBMS_ ROWID package is created by $ORACLE_HOME/rdbms/admin/dbmsutil.sql, which is actually included in catproc.sql. Provides some functions for handling 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; converts restricted rowid to extended rowid, which is used to convert old ROWID to ORACLE8 formats.

DBMS_ROWID.ROWID_TO_RESTRICTED converts extended ROWID to restricted ROWID.

DBMS_ROWID.ROWID_VERIFY determines whether a restricted ROWID can be converted to an extended format

When DBMS_ROWID.ROW_INFO is used to interpret ROWID, you can get DATA OBJECT NUMBER,RELATIVE FILE NUMBER,BLOCK NUMBER and ROW NUMBER.

DBMS_ROWID.CREATE generates ROWID.

ROWNUM:

In Oracle, to query the first N records according to specific conditions, you can do it with a rownum. Select * from emp whererownum 5 failed. To know why it failed, you need to understand the mechanism behind rownum: 1 Oracle executes your query.

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

Knowing the principle, you know 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.

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

For rownum, it is the number that the oracle system assigns sequentially to the rows returned from the query, with the first row assigned 1, the second row 2, and so on, this pseudo field can be used to limit the total number of rows returned by the query, and rownum cannot be prefixed with the name of any table. For example: table: student (student) table, the structure of the table is: ID char (6)-- student number name VARCHAR2 (10)-- name create table student (ID char (6), name VARCHAR2 (100)); insert into sale values ('200001Zhangyi'); insert into sale values ('200002Jing' Wang er'); insert into sale values ('200003Zhi' Li San'); insert into sale values ('200004Zhi' Zhao Si') Commit; (1) rownum for the query condition equal to a certain value, if you want to find the first student information in the student table, you can use rownum=1 as a condition. However, if you want to find the second item of student information in the student table, you can't find the data using rownum=2. Because rownum starts from 1, but natural numbers above 1 are considered to be false conditions when rownum makes a judgment of yes, so it is impossible to find rownum= n (the natural number of n > 1). SQL > selectrownum,id,name from student whererownum=1; (can be used to limit the number of records returned to ensure no errors, such as implicit cursors) 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 records after the second row of records, you cannot find records when using rownum > 2 The reason is that because rownum is a pseudo column that always starts with 1, Oracle thinks that the condition of rownum > n (the natural number of n > 1) is still not true, so there is no record SQL > selectrownum,id,name from student whererownum > 2. ROWNUMID NAME-so how can we find the record after the second line? You can use the following subquery methods to solve the problem. Note that the rownum in the subquery must have an alias, otherwise the record will not be found, because rownum is not a column of a table, and without an alias, it is impossible to know whether rownum is a column of a subquery or a column of the main query. 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 less than a certain value if you want to find the previous record of the third record, use rownum selectrownum,id,name from student whererownum select * from (selectrownumno,id,name from student whererownum=2 NO ID NAME-2 200002 Wang 2 3 200003 Li San (4) rownum and sorting rownum in Oracle are the sequence numbers generated when fetching data, so you must pay attention to the specified rowmun row data if you want to specify the sorted data. 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, we can see that rownum is not a serial number generated according to the name column. The system numbers the records in the order in which they were inserted, and the rowid is also assigned 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 thus sorted by name and marked with the correct serial number (small to large) with rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.