Analysis of Differences between rownum and rowid in Oracle Query Statement

This paper mainly introduces the differences between rownum and rowid in Oracle query in the form of examples, and introduces the specific contents of the first record when the query condition is rownum = 2, taking the hypothetical way as an example.

In the query, we can notice that something similar to

select xx from table where rownum < n (n>1) 

Such a query has the correct meaning, and

select xx from table where rownum = n

Such a query only holds when n=1,

select xx from table where rownum > n (n>1) 

Such a query can only get 1 empty set.

In addition

select xx from table where rownum > 0

This query returns all records. Why is this? The reason lies in the processing of rownum by Oracle. rownum is generated when the result set is obtained, which is used to mark a field in the result order of the result set. This field is called "pseudo sequence", that is, a sequence that does not exist in fact. It is characterized by sequential marking and successive increment, in other words, only if there is a record of rownum=1, it is possible to have a record of rownum=2.

Let's go back and analyze the case of using rownum as the query condition of Oracle in where in 1. When rownum = 1, or rownum < = n (n > 1), no problem. So why is it when the condition is rownum = n or rownum > = When there is data in n, you can only get one empty set? Assuming that our query condition is rownum = 2, when the first record is found, Oracle marks this record rownum as 1, and it is found that it does not match the condition of rownum = 2, so the result set is empty.
If there is a query statement that is

select xx,yy from table where zz > 20 and rownum < 10

Then, when executing, it is first according to zz > 20 criteria query out a result set, and then take out the first 10 items according to rownum and return? Still following zz > The condition of 20 is queried first, and then one record is marked with one rownum to rownum < Stop inquiring at 10? My personal feeling should be the latter, that is, when executing statements, I don't do full scan, but stop querying when I get enough data.
To verify this idea, it should be very simple to find a table with a very large amount of data and query Oracle. Unfortunately, I don't have such a table at present, so qualified readers can test it by themselves.

We can see that the direct use of rownum is limited. However, it is easy to encounter the need to "find out the qualified records from Article xx to Article xx", such as page paging. At this time, how to construct a result set that suits you?
Of course, it is also possible to take all of them out and select them manually, but the premise is that the number of data pieces in the whole data set is small. If you encounter 100 million pieces of data and take them out, users don't have to do anything else. What should users do at this time? Of course, we need to use the rownum we introduced! Isn't rownum a "pseudo-sequence"? Well, we can make it a real field now.

The specific method is to use the sub-Oracle query, and when building a temporary table, the rownum is also constructed. For example

select xx,yy from (select xx,yy,rownum as xyz from table where zz >20) where xyz between 10 and 20

That's it.

In addition, it can be done by using the result set processing function minus provided by Oracle, 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 sequence: rowid. However, rowid is different from rownum. Generally speaking, rowid corresponding to each row of data is fixed and only 1, which is determined when this row of data is stored in the database. You can use rowid to query records, and using rowidOracle to query records is the fastest query method.

I haven't tried this, and it is very difficult to remember a string with a length of 18 bits and no obvious regularity, so I personally think it is not very practical to query records by using rowid. In addition, rowid changes only after table movement (such as table space change, data import/export).