Home » SQL & PL/SQL » SQL & PL/SQL » ROWID reliability question. (10.2.0.1)
ROWID reliability question. [message #325757] Sun, 08 June 2008 06:47 Go to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
If I have a final table which is updated by a periodicly executed Stored Procedure which reads a transactions (intermediate)
table that holds the rowid of the source table that was read by a trigger that populated the transactions table, can the
final table be filled with records from the source table just by using the rowid stored in the transactions table ?
I would like to know if this would be a reliable solution.

source_table -> trigger --(rowid)--> transactions_table --(rowid)-----> stored proc -> final_table.
     |                                                                     |
     +------------>--------- on rowid based data ---------------->---------+
Re: ROWID reliability question. [message #325759 is a reply to message #325757] Sun, 08 June 2008 07:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
From here :

Quote:
Physical Rowids

Physical rowids provide the fastest possible access to a row of a given table. They contain the physical address of a row (down to the specific block) and allow you to retrieve the row in a single block access. Oracle guarantees that as long as the row exists, its rowid does not change. These performance and stability qualities make rowids useful for applications that select a set of rows, perform some operations on them, and then access some of the selected rows again, perhaps with the purpose of updating them.


But keep in mind, that that only applies to short term storage of rowids like in your example, where you can make sure that they are not stored across table reorganizations or database imports/exports for example, since that will of course change them.

So you will have to have all of the entries in your transaction table processes and the transaction table must be empty before you do any database maintenance or upgrades that might change the rowids.


Re: ROWID reliability question. [message #325762 is a reply to message #325757] Sun, 08 June 2008 07:52 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Thanks Thomas,

I'm aware of the long/short term storage of the row id's like you mentioned. The transactions will live for at most 15 minutes. Within that time all records will have been processed.

Walter
Re: ROWID reliability question. [message #325765 is a reply to message #325757] Sun, 08 June 2008 08:27 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must:
- lock the rows
- if the table is a partitioned one, you must not change the partition key
- if the table is an IOT, you must not change the index key

Regards
Michel
Previous Topic: problem avoiding Mutating trigger
Next Topic: How to put source code into procedure ?
Goto Forum:
  


Current Time: Fri Dec 09 21:29:19 CST 2016

Total time taken to generate the page: 0.08223 seconds