Re: Comparing two rowids

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 29 Nov 2006 13:08:13 -0700
Message-Id: <20061129200749.AA9014C2AE8@turing.freelists.org>

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 /
Yes
No

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;

CAS

---
No

1 row selected.

SQL>

If proper rowids are used the "bug" disappears.

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


>Guys,

>

>Thanks.

>

>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.    :-)

>


Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 



______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 29 2006 - 14:08:13 CST