Re: Comparing two rowids

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: contactarul@xxxxxxxxx
  • Date: Wed, 29 Nov 2006 13:08:13 -0700

You'd certainly want them to be different, so obviously the equality/non-equality comparison is valid and important. A rowid contains the file_number (numeric), block number (numeric) and row number (also numeric), the new extended rowid also contains the object_id (again numeric). So since the "components" of the rowid are all numeric you certainly can order them. Whether that makes sense depends on the question/purpose of the comparison. Same with colours. One way to order them can be by frequency and then we have "infrared" (below red !!) and "ultraviolet" (above violet).

I believe the discrepancy is due to a conversion issue from the character representation of the rowid to a rowid type.:

select dbms_rowid.rowid_relative_fno(chartorowid('AAAKPkAAKAAARs7AAC')) FNO, dbms_rowid.rowid_block_number(chartorowid('AAAKPkAAKAAARs7AAC')) BNO, dbms_rowid.rowid_row_number(chartorowid('AAAKPkAAKAAARs7AAC')) RNO from dual;
       FNO        BNO        RNO
---------- ---------- ----------
        10      72507          2

select dbms_rowid.rowid_relative_fno(chartorowid('AAAKPkAAKAAARs+AAA')) FNO, dbms_rowid.rowid_block_number(chartorowid('AAAKPkAAKAAARs+AAA')) BNO, dbms_rowid.rowid_row_number(chartorowid('AAAKPkAAKAAARs+AAA')) RNO from dual;
       FNO        BNO        RNO
---------- ---------- ----------
        10      72510          0

These are restriced rowids, so no object id.

SQL> declare
  2     rowid1 rowid := chartorowid('AAAKPkAAKAAARs7AAC');
  3     rowid2 rowid := chartorowid('AAAKPkAAKAAARs+AAA');
  4     rid1 rowid := dbms_rowid.rowid_create(0,0,10,72507,2);
  5     rid2 rowid := dbms_rowid.rowid_create(0,0,10,72510,0);
  6  begin
7 dbms_output.put_line(case when rowid1 >= rowid2 then 'Yes' else 'No' end);
  8     dbms_output.put_line(case when rid1 >= rid2 then 'Yes' else 'No' end);
  9  end;
 10  /

SQL> create table arul ( rowid1 rowid, rowid2 rowid );

Table created.

SQL> insert into arul select dbms_rowid.rowid_create(0,0,10,72507,2),dbms_rowid.rowid_create(0,0,10,72510,0) from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select case when rowid1 >= rowid2 then 'Yes' else 'No' end from arul;


1 row selected.


If proper rowids are used the "bug" disappears.

At 12:41 PM 11/29/2006, Arul Ramachandran wrote:


Say a table spans four datafiles, rowid being the physical address of the row, comparing the rowid of one row in datafile 1 with the rowid of another row in datafile 4 did not make sense to me. I think it is kind of comparing my street address in CA with Jared's street address in OR. :-)


Wolfgang Breitling
Centrex Consulting Corporation

This email has been scanned by the MessageLabs Email Security System.
For more information please visit ______________________________________________________________________

Other related posts: