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

In this issue, the editor will bring you about how to analyze ROWNUM and ROWID in Oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. ROWNUM

Definition: ROWNUM is a pseudo column that identifies the order in which select returns records when it queries data from a table or a set of JOIN tables.

When Oracle executes a select query, it assigns a sequence number to row in the order in which the row is returned:

The sequence number of the first row returned is 1, the sequence number of the second row is 2, and so on.

The serial number is the rownum of each row.

Note 1: in the same query statement, if the ORDER BY clause is included after ROWNUM:

Oracle first returns the results without ORDER BY processing, allocates ROWNUM, and then sorts it according to the requirements of the ORDER BY clause.

Therefore, the order of ROWNUM in the returned results is out of order. Such as:

SQL > SELECT ROWNUM,DEPT.* FROM SCOTT.DEPT ORDER BY DNAME

ROWNUM DEPTNO DNAME LOC

--

1 10 ACCOUNTING NEW YORK

4 40 OPERATIONS BOSTON

2 20 RESEARCH DALLAS

3 30 SALES CHICAGO

The result of not applying ORDER BY is:

SQL > SELECT ROWNUM,DEPT.* FROM SCOTT.DEPT

ROWNUM DEPTNO DNAME LOC

-

1 10 ACCOUNTING NEW YORK

2 20 RESEARCH DALLAS

3 30 SALES CHICAGO

4 40 OPERATIONS BOSTON

To make the ROWNUM after ORDER BY contiguous, put ORDER BY into a subquery, such as:

SQL > SELECT ROWNUM,T.* FROM (SELECT DEPT.* FROM SCOTT.DEPT ORDER BY LOC) T

ROWNUM DEPTNO DNAME LOC

--

1 40 OPERATIONS BOSTON

2 30 SALES CHICAGO

3 20 RESEARCH DALLAS

4 10 ACCOUNTING NEW YORK

Note 2: apply >, > =, =, between...and conditions to ROWNUM, and the returned results are all empty:

Because, when the first record is returned, as the first item of the result, the rownum=1 is allocated

When applying >, > =, =, between...and condition judgment, if the condition is not met, the record is deleted.

When the next record is returned, it is still the first of the result, the allocation of rownum=1 still does not meet the condition, and so on, the result is empty.

Select rownum from test where rownum=1;// returns 1 record (the first item in the result set)

Select rownum from test where rownum=2

/ / 0 messages are returned. According to the definition of ROWNUM, it is not difficult to see that when the first record is returned, it is filtered out because of ROWNUM=1.

Select rownum from test where rownum > 10

Select rownum from test where rownum between 2 and 4

Application:

1) you can limit the number of records (rows) that return the result through ROWNUM

SQL > select rownum from test where rownum UPDATE table_name SET column_name = ROWNUM

/ / specify rownum as the value of a column in the row.

II. ROWID

(1) concept: the ROWID of the row identifies the address of the data of the row, and the ROWID contains the following information:

Data object number,

This line of data, the block number in the data file

The position of the row in the data block (first behavior 0)

The number of the data file in which the row of data is saved (the first data file is 1)

ROWID is created when the data is inserted and deleted when the data is deleted.

Cannot manually set or delete an index built from within ROWID,ORACLE via ROWID

(2), type

1. Physical ROWID: holds the addresses of rows in normal tables (table IOT without index organization), aggregate tables (clustered table), partitioned and sub-partitioned tables, indexes, partitions and sub-partition indexes

2. Logical ROWID: save the address of the index organization table (IOT)

(3) physical ROWID

1. Type: physical ROWID includes extended ROWID and restricted ROWID

1), extended ROWID (extended rowid)

Supports block addresses associated with tablespaces and effectively identifies partitioned tables, partitioned indexes, and rows in regular tables and indexes.

Extended rowid is supported in Oracle 8i and later.

2), restricted ROWID (restricted rowid)

For backward compatibility, such as oracle 7 and earlier.

2. Extend ROWID (extended rowid)

a. The extended ROWID uses base64 to encode the physical address of each row of data, including Amurz ZMagol 0-9 Magi + and /. The query is as follows:

SQL > select rowid,dept.* from dept

ROWID DEPTNO DNAME LOC

AAAMfKAAEAAAAAQAAA 10 ACCOUNTING NEW YORK

AAAMfKAAEAAAAAQAAB 20 RESEARCH DALLAS

AAAMfKAAEAAAAAQAAC 30 SALES CHICAGO

AAAMfKAAEAAAAAQAAD 40 OPERATIONS BOSTON

b. Extended ROWID format

Extended ROWID consists of 18 bits, including 4 parts, OOOOOOFFFBBBBBBRRR

A) 000000: the data object number that identifies the segment in the database

B) FFF: the data file number related to the tablespace

C) BBBBBB: block number in the data file

D) RRR: row number in the data block

C. DBMS _ ROWID package

A) dbms_rowid.rowid_object obtains the object number of the data through ROWID

SQL > select dbms_rowid.rowid_object ('AAAMfKAAEAAAAAQAAC') as object_id from dual

OBJECT_ID

-

51146

B) dbms_rowid.rowid_relative_fno obtains the data file number through ROWID

SQL > select dbms_rowid.rowid_relative_fno ('AAAMfKAAEAAAAAQAAC') as file_no from dual

FILE_NO

-

four

C) dbms_rowid.rowid_block_number obtains the block number of the data through ROWID

SQL > select dbms_rowid.rowid_block_number ('AAAMfKAAEAAAAAQAAC') as block_number from dual

BLOCK_NUMBER

-

sixteen

The rowid of large file tablespaces is different from that of small file tablespaces, so to get the correct rowid, you need to add a new parameter ts_type_in, such as:

SQL > SELECT DBMS_ROWID.rowid_block_number (ROWID,'BIGFILE') FROM foo;DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID,'BIGFILE')-24

D) dbms_rowid.rowid_row_number obtains the row number in the data block through ROWID

SQL > select dbms_rowid.rowid_row_number ('AAAMfKAAEAAAAAQAAC') as row_no from dual

ROW_NO

-

two

3. Restricted ROWID (resticted rowid)

a. Restricted rowid represents the physical address of each row of data in binary, and when using SQL Plus queries, the binary representation is converted to varchar2 or hexadecimal representation.

SQL > select dbms_rowid.rowid_to_restricted (rowid,1) as restricted_rowid,dept.* from scott.dept

RESTRICTED_ROWID DEPTNO DNAME LOC

00000010.0000.0004 10 ACCOUNTING NEW YORK

00000010.0001.0004 20 RESEARCH DALLAS

00000010.0002.0004 30 SALES CHICAGO

00000010.0003.0004 40 OPERATIONS BOSTON

b. Restricted rowid format:

A total of 16 bits, including 3 parts: AAAAAAAA.BBBB.CCCC

A) AAAAAAAA: the block number in which the row of data is saved

B) BBBB: the row number of the row of data in the data block

C) CCCC: the data file number that contains the data for that row

(4) logical ROWID (logical rowid)

1. Overview: in the table (IOT) organized by the index, the row is saved in the leaf node of the index and can be moved within or between blocks.

Therefore, these rows do not have a fixed physical address and cannot be uniquely identified based on the physical address.

Oracle provides logical ROWID to identify rows in IOT. Logical ROWID is the primary key based on the table.

Oracle can create a second index for IOT based on these logical ROWID.

The logical ROWID used by each second index contains a physical guess

Physical guess identifies the block location of each row in the IOT when the second index is created

3. ROWID-related package, DBMS_ROWID

SubprogramDescription

ROWID_BLOCK_NUMBER

Returns the block number of a ROWID

ROWID_CREATE

Creates a ROWID, for testing only

ROWID_INFO (process)

Returns the type and components of a ROWID

ROWID_OBJECT

Returns the object number of the extended ROWID

ROWID_RELATIVE_FNO

Returns the file number of a ROWID

ROWID_ROW_NUMBER

Returns the row number

ROWID_TO_ABSOLUTE_FNO

Returns the absolute file number associated with the ROWID for a row in a specific table

ROWID_TO_EXTENDED

Converts a ROWID from restricted format to extended

ROWID_TO_RESTRICTED

Converts an extended ROWID to restricted format

ROWID_TYPE Function

Returns the ROWID type: 0 is restricted, 1 is extended

ROWID_VERIFY

Checks if a ROWID can be correctly extended by the ROWID_TO_EXTENDED function

The above is the editor for you to share how to analyze ROWNUM and ROWID in Oracle, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.