Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Comparing two rowids

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US