Re: rowid value

From: Ryan January <rjanuary_at_gmail.com>
Date: Tue, 5 Aug 2014 10:48:05 -0500
Message-Id: <57B13903-E49C-4B40-AA93-8E17426D86CF_at_gmail.com>

Does higher in this context mean "more recent", or stored in a later block?

Row ID is calculated using the object_id, file, block, and relative block row number. You can determine which row is stored later in the block, but I'm not aware of a way to determine which came first.

As a side note: with row dependencies enabled on a table you can query ora_rowscn to determine the scn of the changed row. Without having row dependencies enabled I'm not aware of a 100% accurate method to track which row came first, as ora_rowscn will be the last modified time of the storage block. http://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns007.htm)

Doing a quick test using DBMS_ROWID.ROWID_CREATE shows that they're likely sorted alphabetically increasing.

block 1: row 1: AACCE4AAEAAAAdRAAB
block 1: row 2: AACCE4AAEAAAAdRAAC
block 2: row 1: AACCE4AAEAAAAdZAAC

SQL> create table tab1 (cola varchar2(200));

Table created.

SQL> insert into tab1

    select rpad(owner||'.'||object_name,200,'x') from all_objects ; 2 3

71163 rows created.

SQL> commit;

Commit complete.

SQL> -- find object id
select object_name, object_type, object_id from dba_objects where object_name = 'TAB1';SQL>

OBJECT_NAME OBJECT_TYPE OBJECT_ID

---------------  ------------------- ----------
TAB1		 TABLE			 532792

SQL> -- find start and end block of an extent for segment in question SELECT FILE_ID, BLOCK_ID START_BLOCK, BLOCK_ID + BLOCKS END_BLOCK FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'TAB1' AND EXTENT_ID = 0;SQL> 2    FILE_ID START_BLOCK END_BLOCK
---------- ----------- ----------

         4 1873 1881

SQL> -- get rowid for block row 1
begin
 dbms_output.put_line (
   DBMS_ROWID.ROWID_CREATE (

     rowid_type    => 1,
     object_number => 532792,
     relative_fno  => 4,
     block_number  => 1873,
     row_number    => 1)

);
end;
/
AACCE4AAEAAAAdRAAB

PL/SQL procedure successfully completed.

SQL> -- get rowid for block row 2
begin
 dbms_output.put_line (
   DBMS_ROWID.ROWID_CREATE (

     rowid_type    => 1,
     object_number => 532792,
     relative_fno  => 4,
     block_number  => 1873,
     row_number    => 2)

);
end;
/
AACCE4AAEAAAAdRAAC

PL/SQL procedure successfully completed.

SQL> -- get rowid for row 1 in next block begin
 dbms_output.put_line (
   DBMS_ROWID.ROWID_CREATE (

     rowid_type    => 1,
     object_number => 532792,
     relative_fno  => 4,
     block_number  => 1881,
     row_number    => 2)

);
end;
/
AACCE4AAEAAAAdZAAC

PL/SQL procedure successfully completed.

SQL> On Aug 5, 2014, at 10:07 AM, Zelli, Brian <Brian.Zelli_at_RoswellPark.org> wrote:

> Another dba I’m covering for told me to delete a duplicate with the higher rowid?
> Which one is the higher rowid? AAAocvACXAAAPFJAAa or AAAocvACXAAAPFJAAb?
>
>
> Brian
>
>
>
> This email message may contain legally privileged and/or confidential information. If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited. If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you.

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

Received on Tue Aug 05 2014 - 17:48:05 CEST