Usage and differentiation of rownum and ROWID in Oracle

1.rownum is a pseudo-column, which is added after getting the query result set (gets a record plus a rownum). Adds a 1-based serial number to the qualifying result.

eg

Select  from Ur_user_info where rownum < 6;

Attention

RowNum is dynamic, it must have query results, and then add this column to the result set of the query. For example: the first record of the rownum is 1, the second is 2, and so on.

Select  from where > 5  and < ten;  ---Query result is an empty set

When a result set is produced, Oracle generates a record of rownum 1, which clearly does not meet the criteria; then a second record is generated, the same rownum=1, and does not conform to the record; keep going, leading to the result set of the resulting SQL generated by the last time set.

If you need to query the results, you need to use a subquery:

Select rownum, Phone_no    from (Selectfromwhere>5    and < ten;

2.ROWID Pseudo-Column

rowID is a physical existence, the actual existence of a column, is a data type.   An ID based on 64 encoded 18 characters that uniquely identifies the physical location of a record.

Uniquely identifies the physical location of the corresponding store, similar to the hashcode value.

Attention:rowid is not stored in the table, so it does not support adding or deleting operations, only user queries.

Select  from where > 5  and < ten;

Real-World scenarios: data deduplication-When multiple records have the same primary key or multiple records are identical, only one record is left. (There are more than one data in the Bill table)

 delete  from   Bal_acctbook_info  where  rowid not  in  (select  min   (ROWID)  from   Bal_acctbook_info  where  balance_id =   " 4012562452                          " and  op_time =   " 20171212111111  "); 

-----Tomorrow on-line, bought yourself a major sickness insurance, I hope all peace.

Usage and differentiation of rownum and ROWID in Oracle