Notes for getting records using rowid and rownum

Notes for getting records using rowid and rownum
We know that rowid and rownum can be used as fake and shoddy in Oracle and are mainly used to locate specific records in the table, but they are different, rowid corresponds to the physical address of the row record, while rownum is not. It judges the first record by the returned record set, that is, the record with rownum = 1, and then goes to the record after fetch, rownum = 2, rownum = 3 ......, And so on. Next let's take a look at the experiment: SQL> conn zlm/zlm
Connected. SQL> create table test1 as select object_id, object_name from dba_objects where rownum <11; Table created. note: When creating a table, we also use the where Condition for the rownum <11 pseudo column to obtain 10 records and insert them to the test1 table. SQL> set lin 130 pages 130SQL> col object_name for a30SQL> select * from test1; OBJECT_ID OBJECT_NAME ---------- Limit 20 ICOL $44 I _USER1 28 CON $15 UNDO $29 C_COBJ #3 I _OBJ #25 PROXY_ROLE_DATA $39 I _IND1 51 I _CDEF2 26 rows $ _ 1 10 rows selected.
Use * to represent all columns. At this time, the rowid and rownum pseudo columns are not displayed. This is the origin of the term "pseudo column", which is false. fake's, we can use it, but it is not stored as data in the table SQL> select rowid, rownum, object_id, object_name from test1; rowid rownum OBJECT_ID OBJECT_NAME ------------------ ------------ hour AAAM + hour 1 20 ICOL $ AAAM + hour 2 44 I _USER1AAAM + hour 3 28 CON $ AAAM + hour 4 15 UNDO $ aabj + hour 5 29 C_COBJ # AAAM + rA AGAAAACUAAF 6 3 I _OBJ # AAAM + Route 7 25 PROXY_ROLE_DATA $ AAAM + Route 8 39 I _IND1AAAM + Route 9 51 I _CDEF2AAAM + Route 10 26 route $ _ 1 10 rows selected. specify all column names. You can see that the content of the rowid and rownum columns is also displayed. Can we use the rowid column as the search condition? Yes, of course, but the premise is that you need to know how the rowid value is distributed. SQL> select rowid, object_id, object_name from test1 where rowid <to_char ('aaam + raagaaaacuaak '); ROWID OBJECT_ID OBJECT_NAME ------------------ export AAAM + route 20 ICOL $ AAAM + Route 44 I _USER1AAAM + Route 28 CON $ AAAM + Route 15 UNDO $ AAAM + Route 29 C_COBJ # AAAM + Route 3 I _OBJ # OBJ # AAAM + rAAGAAAACUAAG 25 PROXY _ ROLE_DATA $ AAAM + limit 39 I _IND1AAAM + limit 51 I _CDEF2AAAM + limit 26 limit $ _ 1 10 rows selected. Why is the condition <to_char ('aaam + raagaaaacuaak ')? As we can see from the observation just now that the first record in Table test1 is J, we need to obtain these 10 records, the records smaller than AAK indicate the meanings of these letters in rowid: in Oracle 8 or lower, rowid (also called restricted rowid) is: FFFF. BBBBBBBB. RRRR occupies 6 bytes (10bit file # + 22bit + 16bit). However, to expand the data file, change the rowid to OOOOOOFFFBBBBBBRRR, occupies 10 bytes (32bit + 10bit rfile # + 22bit + 16bit ). Here, O is the Object number, F is the File number, B is the Block number, and R is the Row number. Since the composition of rowid changes from file # To rfile #, the limit on the number of data files cannot exceed 1023 in the entire database, but cannot exceed 1023 data files in each tablespace. The object_id here is an information related to the physical storage location of a segment, because a segment object can only be in one tablespace. object_id can uniquely confirm ts #, object_id + rfile # can finally locate the specified physical data file where the rowid is located.
Therefore, the AAG indicates that this is a 6th data file. Note that AAA starts counting from 0, and the same is true for row numbers. Therefore, the AAA-AAJ represents 1-10 rows of records in the test1 table (0-> 9)
SQL> col name for a45SQL> select file #, name from v $ datafile; FILE # NAME ---------- upload 1/u01/app/oracle/oradata/ora10g/system01.dbf 2/u01/app/oracle/oradata/ora10g/undotbs01.dbf 3/u01/app/oracle/oradata /ora10g/g04/u01/app/oracle/oradata/ora10g/users01.dbf 5/u01/app/oracle/oradata/ora10g/example01.dbf 6/u01/app/oracle/oradata/ ora10g/zlm01.dbf zlm the default tablespace is zlm, the corresponding data file is zlm01.dbf. We can see that file # is 6, not 7. We can also use the dbms provided by Oracle. the rowid package obtains object_id #, rfile #, block #, and row # based on the value of rowid. Specific values: SQL> select dbms_rowid.rowid_object ('aaam + rAAGAAAACUAAJ ') object_id #, round ('aaam + rAAGAAAACUAAJ ') rfile #, round ('aaam + rAAGAAAACUAAJ') block #, round ('aaam + rAAGAAAACUAAJ ') row # from dual; OBJECT_ID # RFILE # BLOCK # ROW # --------------- ---------- 53163 6 148 9 Table test1 contains 10th records of 53,163rd objects, 6th files, and 148th blocks (0-> 9) to obtain the first five records in Table test1, use the following method: SQL> select object_id, object_name from test1 where rownum <= 5; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 20 ICOL $44 I _USER1 28 CON $15 UNDO $29 C_COBJ # Or
SQL> select object_id, object_name from test1 where rownum! = 6; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 20 ICOL $44 I _USER1 28 CON $15 UNDO $29 C_COBJ # Everyone can understand the first method, so why rownum! = 6. What about the first five records returned? Isn't that incredible? 7-10 records are also satisfying! = 6 ah, why not show it? Let's take a look at the official explanation of the rowid mechanism:

1 Oracle executes your query.

2 Oracle fetches the first row and callit row number 1.

3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.

4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth ).

5 Go to step 3.

When rownum is used as the query condition, Oracle's SQL engine always searches for the row number 1 record. If no record is found, the row will be discarded until it is found, then there will be row number 2, row number 3 ...... This process continues until the end of the 2nd queries that do not meet the condition is reached, because rownum = 1 has met! If this condition is set to 6, fetch is performed sequentially until rownum = 5 is obtained. All the matching records are returned, and a result is returned, when 6th records are found, they obviously do not match! = 6, then the previous round of loop is over, and the subsequent 7th records no longer contain the required condition of row numer 1, therefore, the subsequent query is directly discarded, that is, 8-10 records will not be fetch again, because no one can obtain rownum = 1.

Understanding of rownum and rowid in Oracle

Oracle rowid

Basic Oracle Tutorial: rowid

Summary of ROWNUM usage in Oracle, differences between ROWNUM and rowid

Rowid captured by index suspect

Oracle uses rownum and rowid for paging

Detailed description of ROWID in Oracle 10g

For more details, please continue to read the highlights on the next page:

1 2 Next Page