RE: Possibility of changing Rowid because of UPDATE operations
- From: "Robson, Peter" <pgro@xxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Mar 2004 10:05:31 -0000
I'm not sure that a completely balanced view on rowid is being presented
here.
For the record, I have been using rowid for various tasks, including
monitoring the same rows through multiple update operations (v7, v8) for
several years, without ANY of the problems alluded to. This use has of
course been against the background of a clear understanding of where the
rowid integrity would break down (and export / import is the obvious
example).
In any event, did not an early version of log miner use rowid?
peter
edinburgh
> -----Original Message-----
> From: Rognes, Sten [mailto:Sten.Rognes@xxxxxxxxxx]
> Sent: Wednesday, March 17, 2004 9:10 PM
> To: 'oracle-l@xxxxxxxxxxxxx'
> Subject: RE: Possibility of changing Rowid because of UPDATE
> operations
>
>
>
> Further to what's been mentioned already... With 10g there is
> a "alter table
> <table_name> shrink space" command. To use this feature you
> need to enable
> row movement hence ROWIDs are very likely to change following
> the shrink.
>
> Sten
>
>
> -----Original Message-----
> From: Prasada.Gunda@xxxxxxxxxxxxxxxx
> [mailto:Prasada.Gunda@xxxxxxxxxxxxxxxx]
>
> Sent: Wednesday, March 17, 2004 8:40 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: Possibility of changing Rowid because of UPDATE
> operations
>
>
>
> Thanks Justin, Mladen, Jay and Tim for your inputs and suggestions.
>
> I agree with you Justin that we can not rely on ROWID
> behavior as it is
> internal to Oracle. I mentioned the same when one of our team
> member had
> came up with this suggestion. That was the reason I sent out
> an email to
> find more about how it works with 9i/10g releases and with
> different types
> of tables (like IOT, Cluster and Partitioned tables etc). I
> really got
> great inputs from all of you.
>
> Justin, we are actually using 9i Change Data Capture(CDC)
> technique and it
> provides all the changes that happen on the source table in the change
> table. But, the problem is when we update our staging table
> on the DW end.
> We have persistent staging area where it maintains the
> history of all the
> changes of source. So, when we see a changed row in the
> change table (CDC),
> we need to terminate the existing row and insert a new row
> (it is Ralph
> Kimball Type 2 method) in the staging table (DW). The way
> the existing
> record gets terminated by using (natural) key of the source
> table. So, if
> the source table does not have a key, there is no easy way to
> update the
> existing row in the staging table except using the 'UU' record of the
> change table to update the existing row(by using all columns
> of 'UU' in the
> where clause of an update statement) in the staging table and
> using 'UN'
> record as a new record. So, one of the team member suggested
> to use the
> rowid (change table keeps track of the rowid) instead of
> using all 'UU'
> record values. I wasn't quite comfortable using rowid for the
> reasons of
> rowid changes and on top of that whenever source system does
> any kind of
> operations on even normal table that causes rowid change
> (export/import,
> alter table.. move etc), it would mess up the staging table
> since we can
> not update the existing row due to change in rowid. This the
> story behind
> sending my original email.
>
> Thank you all again.
>
> Best Regards,
> Prasad
> 860 843 8377
>
>
>
>
> "Justin Cave
>
> (DDBC)" To:
> <oracle-l@xxxxxxxxxxxxx>
>
> <jcave@xxxxxxxxxxx cc:
>
> > Subject: RE:
> Possibility of
> changing Rowid because of UPDATE operations
> Sent by:
>
> oracle-l-bounce@fr
>
> eelists.org
>
>
>
>
>
> 03/16/2004 03:03
>
> PM
>
> Please respond to
>
> oracle-l
>
>
>
>
>
>
>
>
>
> The ROWID is certainly liable to change due to an UPDATE of an IOT. I
> can't think of a reason Oracle would change the ROWID of a
> row in a normal
> table because of an UPDATE, but Oracle certainly does not
> guarantee such a
> thing. It's always possible that some new feature will come along and
> cause ROWID's to change because of an update-- I would avoid
> designing a
> system that depended on hoping that didn't happen.
>
> Have you looked into Oracle Change Data Capture (CDC)? It sounds like
> that's what you're after here. If so, it's a lot easier to use Oracle
> built-in functionality that to write it yourself.
>
>
> Justin Cave
> Distributed Database Consulting, Inc.
> http://www.ddbcinc.com/askDDBC
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Prasada.Gunda@xxxxxxxxxxxxxxxx
> Sent: Tuesday, March 16, 2004 11:53 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Possibility of changing Rowid because of UPDATE operations
>
>
> Hello All,
>
> Do you think of any situation where Oracle would change the
> rowid(or move
> the row that causes rowid change) under the hood in 8i/9i or
> even in 10g if
> there are simply Update operations happen on a row?
>
> I know that the Rowid of the row is not going to change when
> there is a row
> chaining/migration. So, this is not an issue. And, I
> understand that the
> Rowid would change in other situations like row is
> deleted/inserted, table
> is exported/imported, truncated/reloaded or alter table..move
> and other
> similar operations.
>
> But, I am only concerned with Update operations. Even in IOT
> and Cluster
> tables, I believe Oracle would maintain the same rowid if there is an
> Update to a row.
>
> Please let me know your thoughts/suggestions.
>
> Ours is a DW environment and would like to use the rowids to identify
> changes in the source table where there is no primary/unique key.
>
> Thanks in advance,
>
> Best Regards,
> Prasad
>
>
>
>
>
> **************************************************************
> ***********
> PRIVILEGED AND CONFIDENTIAL: This communication, including
> attachments, is
> for the exclusive use of addressee and may contain proprietary,
> confidential and/or privileged information. If you are not
> the intended
> recipient, any use, copying, disclosure, dissemination or
> distribution is
> strictly prohibited. If you are not the intended recipient,
> please notify
> the sender immediately by return e-mail, delete this communication and
> destroy all copies.
> **************************************************************
> ***********
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
> 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
>
>
>
>
>
> **************************************************************
> ***********
> PRIVILEGED AND CONFIDENTIAL: This communication, including
> attachments, is
> for the exclusive use of addressee and may contain
> proprietary, confidential
> and/or privileged information. If you are not the intended
> recipient, any
> use, copying, disclosure, dissemination or distribution is strictly
> prohibited. If you are not the intended recipient, please
> notify the sender
> immediately by return e-mail, delete this communication and
> destroy all
> copies.
> **************************************************************
> ***********
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
*********************************************************************
This e-mail message, and any files transmitted with it, are
confidential and intended solely for the use of the addressee. If
this message was not addressed to you, you have received it in error
and any copying, distribution or other use of any part of it is
strictly prohibited. Any views or opinions presented are solely those
of the sender and do not necessarily represent those of the British
Geological Survey. The security of e-mail communication cannot be
guaranteed and the BGS accepts no liability for claims arising as a
result of the use of this medium to transmit messages from or to the
BGS. . http://www.bgs.ac.uk
*********************************************************************
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
