Differences between rowid and rownum

The difference between rowid and rownum for the rownum problem in Oracle is not supported by many materials>, >=, =,... and, can only use the above symbols (<, <= ,! =), Not to mention>,> =, =, .. and will prompt SQL syntax errors, but often cannot find a record, and it seems to be an inexplicable result, in fact, you should not be surprised to understand the meaning of this rownum pseudo column. Both rowid and rownum are called pseudo columns, but they exist in different ways. rowid can be physical, indicating the unique location ID of the record in the tablespace, it is unique in DB. As long as the record has not been moved, the rowid remains unchanged. Compared with a table, rowid is similar to a common column in a table. Therefore, rowid is not used as a condition to show the inexplicable results of rownum. Rownum Introduction: rownum is a pseudo column added to the result set, that is, a column added after the result set is first found (emphasis: there must be a result set first ). In short, rownum is the serial number that matches the condition result. It always starts from 1. Therefore, the result you select cannot be less than 1, and there are other values greater than 1. In addition, you must note that rownum cannot use any base table name as the prefix. For the following SQL statements

SQL>select rownum,id,age,name from loaddata where rownum > 2;    ROWNUM  ID     AGE  NAME    ------- ------ ---  ------

 

Rownum> 2 has no record, because if the first clause does not satisfy the removal, the rownum of the second record becomes 1, and so on, there will never be a record that meets the condition. It can be understood as follows: rownum is a sequence in which oracle databases read data from data files or buffers. If it obtains the first record, the rownum value is 1, the second record is 2, and so on. If you>,> =, =,... and, because the rownum of the first record obtained from the buffer or data file is 1 and does not meet the SQL statement conditions, it is deleted and then removed, however, its rownum is still 1 and deleted, and so on, there is no data. With the concept of rownum established from different aspects above, we can understand several phenomena of using rownum: (1) select rownum, id, age, name from loaddata where rownum! = 10 why is the first nine data records returned? It is the same as the result set returned by select rownum, id, age, name from loaddata where rownum <10? Because after the result set is queried and 9th records are displayed, all subsequent records are displayed! = 10 or> = 10, so only the first nine records are displayed. It can also be understood that when rownum is 9, the recorded rownum is 10, because the condition is! = 10, so remove it. Then add the record, and the rownum is 10. If so, only the first nine records will be displayed. (2) When rownum> 1 cannot find a record, while rownum> 0 or rownum> = 1 always shows all records, this is because rownum is added after the queried result set, which always starts from 1. (3) Why between 1 and 10 or between 0 and 10 can be found, but between 2 and 10 cannot be obtained for the same reason, because rownum always starts from 1. From the above, it is not right to discard the rownum = 1 record at any time. It is indispensable in the result set. If rownum = 1 is missing, it cannot exist like a castle in the air, therefore, your rownum condition must include 1. The following is an example of the practical use of rownum: SQL table creation script

create table LOADDATA(  ID   VARCHAR2(50),  AGE  VARCHAR2(50),  NAME VARCHAR2(50));

 

(1) rownum: If you want to find the information of the first record in the loaddata table for a query condition equal to a certain value, you can use rownum = 1 as the condition. However, if you want to find the information of the second record in the loaddata table, you cannot find the data using rownum = 2. Because rownum starts from 1, but the natural numbers above 1 are regarded as false when rownum is equal to or equal to the true value. Therefore, rownum = n (The Natural Number of n> 1) cannot be found ).

select rownum,id,age,name from loaddata where rownum = 1

; (It can be used to limit the number of returned records to avoid errors, for example, implicit cursor)

SQL>select rownum,id,age,name from loaddata where rownum = 1;    ROWNUM  ID     AGE  NAME    ------- ------ ---  ------         1  200001  22   AAASQL>select rownum,id,age,name from loaddata where rownum = 2;     ROWNUM  ID     AGE  NAME    ------- ------ ---  ------

 

Note: SQL> select rownum, id, age, name from loaddata where rownum! = 3; the first two records are returned.

    ROWNUM  ID     AGE  NAME    ------- ------ ---  ------         1  200001  22   AAA         2  200002  22  BBB

 

(2) rownum for query conditions greater than a certain value if you want to find records from the second row after the record, when rownum> 2 is used, no records can be found, the reason is that rownum is a pseudo column that always starts from 1. Oracle considers that rownum> n (Natural Number of n> 1) is still not true, so no records can be found.

SQL>select rownum,id,age,name from loaddata where rownum > 2;    ROWNUM  ID     AGE  NAME    ------- ------ ---  ------

 

Then how can we find the record after the second row. You can use the following subquery method to solve the problem. Note that the rownum in the subquery must have an alias; otherwise, the record is not found because rownum is not a column of a table. If the alias cannot be found, you cannot know whether rownum is a subquery column or a primary query column.

SQL>select rownum,id,age,name from(select rownum no ,id,age,name from loaddata) where no > 2;     ROWNUM  ID     AGE  NAME     ------- ------ ---  ------         3  200003  22   CCC         4  200004  22  DDD         5  200005  22   EEE  6  200006  22   AAASQL>select * from(select rownum,id,age,name from loaddata) where rownum > 2;     ROWNUM  ID     AGE  NAME     ------- ------ ---  ------

 

(3) rownum for query conditions smaller than a certain value if you want to find the record before the third record, when using rownum <3, you can get two records. Obviously, rownum considers the condition of rownum <n (Natural Number of n> 1) as true, so records can be found.

SQL> select rownum,id,age,name from loaddata where rownum < 3;     ROWNUM  ID     AGE  NAME     ------- ------ ---  ------         1  200001  22   AAA         2  200002  22  BBB

 

To sum up the preceding situations, you may need to query rownum data in a certain range. We can see that the rownum query condition for a value smaller than a certain value is true, rownum is regarded as false for query conditions greater than a certain value, but it can be converted to true indirectly. Subquery is required. For example, to query the data of rownum between the second row and the third row, including the data of the second row and the third row, we can only write the following statement to first let it return the record rows smaller than or equal to three, then, in the primary query, it is judged that the alias column of the new rownum is greater than or equal to two record rows. However, such operations will affect the speed in the big data set.

SQL> select * from (select rownum no, id, age, name from loaddata where rownum <= 3) where no> = 2; (must be medium, small, and large) rownum id age name ------- ------ 2 200002 22 BBB 3 200003 22 CCC

 

You can also use this method:

SQL>select rownum,id,age,name from loaddata  where  rownum < 4 minus select rownum,id,age,name from loaddata  where  rownum < 2     ROWNUM  ID     AGE  NAME     ------- ------ ---  ------         2  200002  22  BBB         3  200003  22   CCC

 

(4) rownum and rownum in Oracle are the sequence numbers generated when data is retrieved. Therefore, you must pay attention to the number of specified rowmun rows of data to be sorted. Prerequisites: five records have been inserted in the loaddata table, and the last record id is 200005.

insert into loaddata values('200006','22','AAA');SQL>select rownum,id,age,name from loaddata;     ROWNUM  ID     AGE  NAME     ------- ------ ---  ------         1  200001  22   AAA         2  200002  22   BBB         3  200003  22   CCC         4  200004  22   DDD         5  200005  22   EEE         6  200006  22   AAASQL>select rownum ,id,age,name from loaddata order by name;     ROWNUM  ID     AGE  NAME     ------- ------ ---  ------         1  200001  22   AAA         6  200006  22   AAA         2  200002  22   BBB         3  200003  22   CCC         4  200004  22   DDD         5  200005  22   EEE

 

It can be seen that rownum is not the serial number generated by the name column. The system assigns the number of the record row according to the sequence in which the record is inserted, and the rowid is also allocated sequentially. To solve this problem, you must use the subquery

SQL>select rownum ,id,age,name from (select * from loaddata order by name);     ROWNUM  ID     AGE  NAME     ------- ------ ---  ------         1  200001  22   AAA         2  200006  22   AAA         3  200002  22   BBB         4  200003  22   CCC         5  200004  22   DDD         6  200005  22   EEE

 

In this way, the sequence is sorted by name, and the correct sequence number (from small to large) is marked with rownum. For large data volumes, we recommend that you add a primary key or index to the order by field to improve the efficiency. similarly, an intermediate record set is returned:

SQL> select * from (select rownum ro, id, age, name from loaddata where rownum <5 order by name) where ro> 2 (select first and then sort and then select) rownum id age name ------- ------ --- ------ 3 200002 22 BBB 4 200003 22 CCC

 

Instance requirement: if you do not know the data rules and quantity in the database, print all the student data to the terminal. Solution: rownum is a pseudo column and does not exist in the table. The database first executes the from book traversal to the book table. If there is no where filter condition, it first generates a result set, then let's look at the conditions after the select statement to pick out the appropriate fields to form the final result set. If the where condition exists, those that do not meet the condition will be deleted from the first result set, the following data is added for determination. Therefore, if rownum = 2 is written directly or rownum> 10 is used, no data can be found, but a subquery can be used to solve this problem, for select rownum, id from book where rownum = 2; is the result of data query.

declare       v_number binary_integer;       v_student student%rowtype;begin     select count(*) into v_number from student;     for i in 1..v_number loop         select id,name,age into v_student from(select rownum rn,id,name,age from student)where rn=i;         dbms_output.put_line('id: '||v_student.id||' name:'||v_student.name);     end loop;end;