Rowid and rownum in the Oracle database

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. The differences are shown in the following example.

Rowid is 'string' type, and rownum is 'number' type

Rowid is a physical address, which is generated when data is inserted.

Rownum is generated during query and is accumulated from 1,

Select int_id, rowid from bts_x where rowid> '0' (the query result is all)

Select int_id, rowid from bts_x where rowid> 0 (statement error, Type mismatch)

Select int_id from bts_x where rownum> 1 the query result is zero.

Select int_id from bts_x where rownum> 0 (> = 1) the query result is all

Select int_id from bts_x t where rownum <20 (19 query results)

Select int_id from bts_x where rownum <40 and rownum> 20 (the query result is 0)
Select int_id from bts_x where rownum> 20 and rownum <40 (the query result is 0)

Obtain 11th to 20 data records based on rownum:

Correct syntax:

Select int_id, XYZ
From (select int_id, rownum XYZ
From (select int_id from bts_x t order by int_id DESC) T1)
Where XYZ> 10
And XYZ <= 20

Incorrect syntax:

Select int_id, rownum XYZ
From (select int_id from bts_x t order by int_id DESC) T1
Where rownum> 10 and rownum <20 (0 data records are found, and the error is in rownum> 10 and rownum <= 20)

The above is the test result.

 

Reposted on the Internet as follows:

In Oracle, there is a very interesting thing, that is, rownum. When you query data from a table, the returned result set will contain the rownum field, and sometimes you can use rownum for some conditional queries.

In the query, we can note that a query similar to "select XX from table where rownum <n" (n> 1) has a correct meaning, the query "select XX from table where rownum = N" is only valid when n = 1. "select XX from table where rownum> N" (n> 1) such a query can only obtain one empty set. In addition, the query "select XX from table where rownum> 0" returns all records. Why? The reason is that in Oracle's processing of rownum, rownum is generated when the result set is obtained. It is used to mark a field in the result sequence of the result set. This field is called a "pseudo series ", that is, a series that does not actually exist. It is characterized by sequential labeling and incremental addition. In other words, only records with rownum = 1 can have records with rownum = 2.

Let's look back and analyze the use of rownum as the query condition in where. When rownum is equal to = 1 or rownum <= n (n> 1), no problem occurs. So why can only one empty set be obtained when the condition is rownum = N or rownum> = n? Assume that our query condition is rownum = 2. When the first record is queried, Oracle marks rownum as 1, and the result shows that it is inconsistent with the condition where rownum = 2, the result set is empty. Here, I suddenly had an interesting idea: Assume that a query statement is
XX, YY from table where ZZ> 20 and rownum <10, a result set is first queried according to the ZZ> 20 conditions during execution, then retrieve the first 10 results according to rownum? Or is the query started based on the ZZ> 20 condition, and then a record is marked with a rownum, and the query stops when rownum is <10? I think it should be the latter, that is, when executing the statement, instead of doing full scan, it is to stop the query when enough data is obtained. To verify this idea, it should be very easy to find a table with a large amount of data for query. Unfortunately, I do not have such a table.

We can see that the direct use of rownum is restricted. However, it is easy to encounter such a requirement to "find the xx to xx records that meet the conditions", such as page processing. In this case, how can we create a result set suitable for ourselves? Well, the guy on the wall who said they could pick them all up can pull them out. Of course, this can also be done, but the premise is that there are not many data entries in the entire dataset. If 100,000 million pieces of data are obtained, the user will not have to do anything else. What Should users do at this time? Of course, we need to use the rownum pull we introduced! Rownum is not a "pseudo-series". Let's just make it a real field. The specific method is to use subqueries to construct rownum when creating a temporary table. For example, "select
XX, YY from (select XX, YY, rownum as XYZ from table where ZZ> 20) Where XYZ between 10 and 20. In addition, the result set processing function minus provided by Oracle can also be used, for example, "select XX, YY from table where ZZ> 20 and rownum <20 minus select XX, YY from table where ZZ> 20 and rownum <10 ", but using minus seems to consume more resources than using subqueries.

Similar to rownum, Oracle also provides another pseudo-Series: rowid. However, rowid and rownum are different. Generally, the rowid corresponding to each row of data is fixed and unique. It is determined when this row of data is stored in the database. You can use rowid to query records, and query records by rowid is the fastest query method. (I have never tried this, and it is very difficult to remember a string with a length of 18 characters without obvious regularity, therefore, I personally think that it is not very practical to query records using rowid. rowid changes only when the table is moved (such as table space changes, data is imported/exported.