Re: Comparing two rowids

From: "Arul Ramachandran" <contactarul@xxxxxxxxx>
To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 14 Dec 2006 20:27:14 -0800

Hello Wolfgang,

My delayed thanks for your response. (sorry, I was away from email for a few
weeks).

I am quiet convinced with your reply, however I did not get this part.
Please correct me if I am wrong.

These are restriced rowids, so no object id.


I am under the impression that the rowids

AAAKPkAAKAAARs7AAC
AAAKPkAAKAAARs+AAA

are extended rowids that contain the object number.

From the Oracle doc:

"A physical rowid datatype has one of two formats:

  -

  The extended rowid format supports tablespace-relative data block
  addresses and efficiently identifies rows in partitioned tables and indexes
  as well as nonpartitioned tables and indexes. Tables and indexes created by
  an Oracle8i (or higher) server always have extended rowids.
  -

  A restricted rowid format is also available for backward compatibility
  with applications developed with Oracle database version 7 or earlier
  releases."

"Extended Rowids

Extended rowids use a base 64 encoding of the physical address for each row
selected. The encoding characters are A-Z, a-z, 0-9, +, and /."
"An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR"


If I consider these as extended rowids and take into account the object
number,  then I run into the discrepancy stated in my original email

Thanks,
Arul

On 11/29/06, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx > wrote:

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
______________________________________________________________________

Re: Comparing two rowids

Other related posts: