RE: rowid value
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <rjanuary@xxxxxxxxx>, <Brian.Zelli@xxxxxxxxxxxxxxx>
- Date: Wed, 6 Aug 2014 04:41:45 -0400
JL's point is excellent and it highlights the actual meaning of duplicate
being all columns of some pair of rows are identical.
IF someone is trying to retrofit a PK onto a table that previously did not
have one or is repairing a bit of blown application level duplication
control of some column set less than the whole row, that is an entirely
different case and using the word "duplicate" is sloppy slang. For non-true
duplicate removal, analysis of which of two unenforced PKs should be kept
based on the column contents is often required.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Tuesday, August 05, 2014 7:19 PM
To: rjanuary@xxxxxxxxx; Brian.Zelli@xxxxxxxxxxxxxxx
Cc: oracle-l (oracle-l@xxxxxxxxxxxxx)
Subject: RE: rowid value
You have to be a bit careful about dbms_rowid.create_rowid() - it doesn't
create rowids it creates strings that look like rowids, and the sorting
order is different. ( http://jonathanlewis.wordpress.com/2013/12/01/rowids/
)
I suspect, though, that this is probably irrelevant for the OP and the DBA
he's covering for. It sounds like the DBA is trying to describe one of the
standard tricks for deleting duplicate rows and if they're duplicate it
doesn't really matter which one is deleted so any comparison which allows
exactly one row to survive is logically correct.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Ryan January [rjanuary@xxxxxxxxx]
Sent: 05 August 2014 16:48
To: Brian.Zelli@xxxxxxxxxxxxxxx
Cc: oracle-l (oracle-l@xxxxxxxxxxxxx)
Subject: Re: rowid value
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@xxxxxxxxxxxxxxx>
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.
Other related posts: