Re: Comparing two rowids

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_at_centrexcc.com > 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
> ______________________________________________________________________
>

--
http://www.freelists.org/webpage/oracle-l