Brief introduction and comparison of ROWID and rownum

About ROWID:

When a user inserts a row of data into a table, Oracle automatically adds a rowid to the row, and each row has a unique rowid,oracle that uses ROWID to locate the data row. ROWID is not explicitly stored as a column of values (pseudo-columns-not the actual data in the table, possibly the internal use of functions or what the information in the block of the row is converted), is the fastest mechanism to access a table of rows. The value of the indexed row and the ROWID value of the index row stored in the index-the actual data.

ORACLE rowID is divided into physical rowid and logical rowid.

See: Get information about data blocks through ROWID

About RowNum:

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 of the use of rownum are summarized as follows:

When using =, only rownum=1 is useful, = other values return the empty set.

When you use < and <=, you can return the rows you want.

When using > and >=, only >=1 returns full table data, others return only the empty set. RowNum for queries that are larger than a value, the rownum>2 is not documented because RowNum is a pseudo column that always starts with 1, and Oracle believes that the condition of rownum> N (the natural number of n>1) is still not tenable, So I can't find the records.

Less than or less equal:

bys@ Bys3>select * from dept where rownum<2;

DEPTNO dname LOC

---------- -------------- -------------

Ten ACCOUNTING NEW YORK

bys@ Bys3>select * from dept where rownum<=2;

DEPTNO dname LOC

---------- -------------- -------------

Ten ACCOUNTING NEW YORK

DALLAS

Greater than or greater than equals:

bys@ Bys3>select * from dept where rownum>=1;

DEPTNO dname LOC

---------- -------------- -------------

Ten ACCOUNTING NEW YORK

DALLAS

SALES CHICAGO

OPERATIONS BOSTON

bys@ Bys3>select * from dept where rownum>1;

No rows selected

bys@ Bys3>select * from dept where rownum>2;

No rows selected

bys@ Bys3>select * from dept where rownum>=2;

No rows selected

Equals:

bys@ Bys3>select * from dept where Rownum=1;

DEPTNO dname LOC

---------- -------------- -------------

Ten ACCOUNTING NEW YORK

bys@ Bys3>select * from dept where rownum=2;

No rows selected

Not equal to:--condition not set up return empty

bys@ Bys3>select * from dept where rownum<>1;

No rows selected

#############

RowNum and ROWID changes in DML operations sample:

The system is the number of records in the order in which they were inserted, and ROWID is also assigned sequentially. --Oracle is ROWID in ascending order when the sort field is not specified in the query

RowNum represents the location of a record in the entire result set, assigned sequentially in the query's result set.

1. Query rowID and ROWID in the line number, rownum

bys@ bys3>select rowid,dbms_rowid.rowid_row_number (ROWID) rowid_num,rownum,dept.* from Dept ORDER by Deptno Desc;

ROWID rowid_num rownum DEPTNO dname LOC

------------------ ---------- ---------- ---------- -------------- -------------

Aaaft7aaeaaaaifaad 3 4 OPERATIONS BOSTON

AAAFT7AAEAAAAIFAAC 2 3 SALES CHICAGO

Aaaft7aaeaaaaifaab 1 2 DALLAS

AAAFT7AAEAAAAIFAAA 0 1 ACCOUNTING NEW YORK

Take one line of records for example analysis:

AAAFT7AAEAAAAIFAAC 2 3 SALES CHICAGO

The rowid of this line is AAAFT7AAEAAAAIFAAC, and by the ROWID algorithm, the 2nd line in the block can be drawn.

But the rownum of this row is 3, which is the sort of result set in the query. It is very intuitive to compare the number of rows in a block of data in a rowid with that of rownum.

2. Delete a piece of data

bys@ bys3>delete Dept where deptno=30;

1 row deleted.

bys@ bys3>commit;

Commit complete.

3. Query rowID and ROWID in the line number, rownum. The line number in rowID and rowID was found to have been deleted, but the rownum was assigned automatically in sequence.

bys@ bys3>select rowid,dbms_rowid.rowid_row_number (ROWID) rowid_num,rownum,dept.* from Dept ORDER by Deptno Desc;

ROWID rowid_num rownum DEPTNO dname LOC

------------------ ---------- ---------- ---------- -------------- -------------

Aaaft7aaeaaaaifaad 3 3 OPERATIONS BOSTON

Aaaft7aaeaaaaifaab 1 2 DALLAS

AAAFT7AAEAAAAIFAAA 0 1 ACCOUNTING NEW YORK