RE: rowid value
Date: Wed, 6 Aug 2014 06:06:20 -0400
Message-ID: <0b4a01cfb15e$12d64c30$3882e490$_at_rsiz.com>
Depending on what “a lot” is, you might be better off with a create table as select (CTAS) keeping non-duplicates (either by select or constraint on the destination), especially if there is a useful physical order for the table.
If the duplicate creating job was some form of direct or there are no routine deletes from the table, then indeed the blocks you’re clearing would all be “high”, so if “a lot” falls in the range too small for CTAS to be cheaper your observation is on point. If the duplicate creating job was not some form of direct and deletes sometimes place blocks back on the freelist or ASSM bitmap as available then you’re subject to that pseudo randomness as to likelihood of emptying blocks based on higher rowid.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kim Berg Hansen
Sent: Wednesday, August 06, 2014 4:16 AM
To: Hemant-K.Chitale_at_sc.com
Cc: Brian.Zelli_at_roswellpark.org; ORACLE-L
Subject: Re: rowid value
Exactly.
If the idea of deleting the "higher" ROWID is to delete the "newest" row, that is not at all certain. But if the rows are identical duplicates, that shouldn't matter ;-)
But supposing there are a *lot* of duplicates to remove (someone by mistake ran a big import job twice, for example), wouldn't always picking "higher" ROWID to delete be somewhat helpful in that there's a bigger chance of clearing space by "emptying" blocks?
Regards
Kim Berg Hansen
kibeha_at_gmail.com
_at_kibeha
On Wed, Aug 6, 2014 at 9:04 AM, Chitale, Hemant K <Hemant-K.Chitale_at_sc.com> wrote:
AAAocvACXAAAPFJAAb is the “higher” ROWID. But it might be holding the “older” row.
Hemant K Chitale
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zelli, Brian
Sent: Tuesday, August 05, 2014 11:07 PM
To: oracle-l (oracle-l_at_freelists.org)
Subject: rowid value
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.
This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 06 2014 - 12:06:20 CEST