Skip to Main Content
  • Questions
  • Is it safe to use ROWID to locate a row?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Alexander.

Asked: December 12, 2005 - 11:15 am UTC

Answered by: Tom Kyte - Last updated: April 23, 2018 - 3:08 pm UTC

Category: Database - Version: 9.2.0

Viewed 100K+ times! This question is

You Asked

Hi Tom,

I'm looking at a client application which retrieves several columns including ROWID, and later uses ROWID to identify rows it needs to update:

update some_table t set col1=value1
where t.rowid = :selected_rowid

Is it safe to do so?
As the table is being modified, can ROWID of a row change?

I did a little bit of research:

create table a(i number, c varchar2(250));
insert into a select some_numeric_column, 'a' from table_500K_rows;
commit;
select rowid r,i,c from a where rownum<100;

then I modified some rows:

update a set c=
';asdfha sdfhaj sdhf asdhlf asldfh ajlskdfh jklasdh fjasdhf jlaksh dfjlashdfjashdfa sdjklfhalsdfhajlsdhflashdflasdhflasd'
where i<200;
commit;

and repeated the same select:
select rowid r,i,c from a where rownum<100;

I got different rows, so I guess pages were split when I updated c to a wide value. But the rowids remained the same:

select rowid r,i,c from a where i<100

gave me the same rows and the same rowids.
Do ROWIDs ever change? If yes, how could I easily reproduce it?

Thank you.


and we said...

A rowid is assigned to a row upon insert and is imutable (never changing) unless the row is deleted and re-inserted (meaning it is another row, not the same row!)

However, rows can be deleted+inserted by various commands "transparently", IF the DBA/table owner has set the "enable row movement" clause on the table.


Meaning a row in a partitioned table - if you update the partition key and the update causes the row to move from one partition to another - the rowid will CHANGE.


If you use alter table t shrink space compact, the rowid could change

If you use alter table t move, the rowid could change (even without enable row movement)

If you use flashback table t to ...., the rowid could change.


It is perfectly safe to use the rowid in ALL CASES however, assuming you combine it with the primary key as well:


update t
set...
where rowid = :x and primary_key = :pk;


we can use the rowid to find the row (fast) and assuming it is still there update it. Note, you need to combine with this some form of LOST UPDATE protection as well !!! You cannot just update by rowid and primary key - unless you locked the row when you read it out, else you will overwrite other peoples changes blindly.

and you rated our response

  (32 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Single update statement?

December 12, 2005 - 2:05 pm UTC

Reviewer: Greg from Toronto

Tom, is this also an issue/concern with a single update statement?

For example:

Update some_table
set value = something
where rowid in ( select rowid from some_table where some_critieria);
??

Just a single statement? The Rollback/Undo is going to keep track of it all for us, correct? In this case, the rowid is "safe", isn't it?


Tom Kyte

Followup  

December 12, 2005 - 2:26 pm UTC

in this case rowid is "safe" but perhaps not "efficient"

update t
set value = something
where some_criteria;


unless the subquery had analytics or something like that.....

What about page splits?

December 12, 2005 - 2:21 pm UTC

Reviewer: Alexander from Chicago

Tom,
thank you for the most useful explanation.
What happened when I updated narrow rows, setting c to wide values? Weren't some pages split? If the pages were split, and ROWIDs did not change, then what is ROWID internally?



Tom Kyte

Followup  

December 12, 2005 - 2:31 pm UTC

the row migrates.

search this site for
"row migration"

to read about it (or see Expert one on one Oracle or Expert Oracle: Database Architecture where I cover this in detail)

The rowid for the row stays the same, even when the row migrates.

Different row, same rowid

December 12, 2005 - 3:01 pm UTC

Reviewer: A reader from San Francisco, CA

Rowid's are also unreliable in the following situation:

Session 1: retrieves a row with rowid X
Session 2: deletes the row with rowid X, commits
/* rowid X is now free for re-use */
Session 3: inserts a new row with rowid X, commits
Session 1: update .... where rowid = X

Session 1's update is not updating the same row that it had earlier retrieved.

Tom Kyte

Followup  

December 12, 2005 - 4:13 pm UTC

that is why you need the primary key as I said above.


....
It is perfectly safe to use the rowid in ALL CASES however, assuming you combine
it with the primary key as well:
.....

Duplicate rowid's

December 12, 2005 - 11:33 pm UTC

Reviewer: Girish from India

But I have seen in Oracle 9i deplicate rowid's for duplicate records , but same is not true in Oracle 8i
Can throw some light on this

SQL> select rownum,rowid,deptno from mydept;

    ROWNUM ROWID                  DEPTNO
---------- ------------------ ----------
         1 AAAJWTAAWAAB+BiAAA         10
         2 AAAJWTAAWAAB+BiAAB         20
         3 AAAJWTAAWAAB+BiAAC         30
         4 AAAJWTAAWAAB+BiAAD         40
         5 AAAJWTAAWAAB+BjAAA         10
         6 AAAJWTAAWAAB+BjAAB         20
         7 AAAJWTAAWAAB+BjAAC         30
         8 AAAJWTAAWAAB+BjAAD         40
 

Tom Kyte

Followup  

December 13, 2005 - 9:00 am UTC

I've a feeling mydept is a union all view IF you really have seen dups - but Bi and Bj are different...

a rowid is unique within a table.
a rowid maybe be duplicated many times across tables in a database, but in a table - nope.



Look twice

December 13, 2005 - 2:14 am UTC

Reviewer: Bart from Berkel en Rodenrijs

Look more carefully !!


If asktom.oracle.com didn't change them, all rowid's are slightly different!

Rowids and GTT

December 13, 2005 - 2:25 am UTC

Reviewer: N.Venkatagiri from India

There is no duplication found in the above rowids.

Tom

Kindly tell me about any issues on using rowids with Global temporary tables.

Tom Kyte

Followup  

December 13, 2005 - 9:16 am UTC

no different than a real table.

Anonymous

November 26, 2008 - 6:17 pm UTC

Reviewer: A reader from CA

Hi Tom,

If you have a single delete statement that deletes many records using rowids. Would there ever be a time when the rowid within this table change during the execution of this delete statement ?

For ex: delete from T where rowid in (<rowids>)

Tom Kyte

Followup  

November 28, 2008 - 5:06 pm UTC

in order for a rowid to change you have to

enable row movement

first - so, if row movement is not enabled, then no.

If it is, then

flashback table could change a rowid, but that is DDL and would not happen concurrently with a delete (so it would not affect it)

alter table t shink space compact, that moves rows and would change rowids...

update of a partition key that causes a row to move. that moves rows and would change rowids





ROWID AND ORDER BY CLAUSE

November 29, 2008 - 11:02 am UTC

Reviewer: Dhairyasheel Tawade from India - Mumbai.

Hi Tom,

just one question regarding your rowid + primary key combination suggestion.

if my table has no primary key and say 10 records in it &
if I do a "select * from mytab order by rowid", will it return the same order everytime?

Also if I fire two inserts in a table, whether the rowid of the 2nd record will be greater than rowid of the 1st record?

Thanks.
Tom Kyte

Followup  

December 01, 2008 - 6:05 am UTC

if rowid is constant, yes it would return the same order each time.

However, in the year 2008, rowid is not constant - many things can cause rowids to change.


As for the last one - the answer is "of course not".

If you

insert A
insert B

and later

insert C
delete A
insert D

it is quite possible that D will be "first" in the table - as it took over A's place.

If rowids always "grew", than space would never be reused (that would be an implication of rowids growing always - we would never be able to reuse old space as the rowid is just a file.block.slot-on-block - a physical address)

Regarding SESSION_ID & SID in Oracle 9i R2

December 02, 2008 - 1:32 am UTC

Reviewer: Rajeshwaran, Jeyabal

Tom,

 Just one question regarding the SESSION_ID & SID. I executed the below query in Oracle 9i R2.

1) SQL> SELECT SYS_CONTEXT('USERENV','SESSIONID') AS SESS_ID FROM DUAL;

SESS_ID
------------------------------------------------------------------------
29058

2) SQL> SELECT * FROM V$MYSTAT WHERE ROWNUM =1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        10          0          1

So now My SESSION_ID is "29058" (This matches with AUDSID column of V$session) & SID is "10"

Then I Issued a Update statement on my Table 'T' and executed the below query.

3) SQL> SELECT xidusn,xidslot,xidsqn,object_id,session_id,locked_mode
 2  FROM V$LOCKED_OBJECT;

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- -----------
         8         21      61670     101115         10           3


Question 1)  What is SESSION_ID and SID in oracle represents?
Question 2)  From the output for the Query 3 why the "SESSION_ID" Column shows the value of 10 instead of "29058" ?

Tom Kyte

Followup  

December 02, 2008 - 7:32 am UTC

sessionid maps to audsid and can be unreliable (eg: for sys recursive transactions, it'll be 0).

sid,serial# uniquely identifies your session


I would stick to using sid,serial#

SESSION_ID & SID IN Oracle 9i R2

December 02, 2008 - 11:05 am UTC

Reviewer: Rajeshwaran, Jeyabal

Tom,

1) Session_id & SID should be used together to identify a Session in oracle server. Or either can be used?

2) Is there is any specific expansion to SID?



Tom Kyte

Followup  

December 02, 2008 - 11:23 am UTC

1) already answered.

"I would stick to using sid,serial# "


2) not at all sure what you mean.

Update Records using ROWID.

August 31, 2009 - 4:12 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Hi Tom,

we have a table named STG_INBOUND. where Pipe Delimited data from flatfile is loaded into the table using SQL loader. Once the data is loaded completely application will start processing the data. once completed the PROCESSED_FLAG in the table is marked to 'Y'. if any error in the data then it will be marked to 'N' and start processing the next record.

the table STG_INBOUND is partitioned by the FILE_KEY Column for each new file loaded by Sql loader that has a reference to the FILE_KEY in STG_FILEMASTER table. once the file is completely processed without errors then partition is Dropped.

ALTER TABLE STG_INBOUND DROP PARTITION <partition_name>;

Question
1) we dont have any primary key in the STG_INBOUND table. we use ROWID to update the PROCESSED_FLAG = Y or PROCESSED_FLAG ='N', is this a right approch to follow? i need your inputs in this scenario.

Also we are not performing any updates on the PARTITION_KEY results in ROW_MOVEMENT & not even issuing ALTER TABLE ...SHRINK SPACE Command during this data processing (even no where in the database itself).

please suggest me is it safe to use ROWID to update records in STG_INBOUND table or need to have a running sequence as a primary key and make use of that in the Where clause?

Tom Kyte

Followup  

August 31, 2009 - 4:32 pm UTC

as long as row movement is not enabled and you do nothing to reorganize the table in any fashion - rowid would be ok in this case. not highly recommended or encouraged, but it would work

Rowid in SYS.COL$

December 23, 2009 - 4:44 am UTC

Reviewer: Manuel Vidigal from Portugal

Hi Tom,

I'm trying to write an application that automatically gives you the alter table scripts from one revision to the other. The problem I'm facing because of the drop and rename column commands, I need a unique identifier for the columns. I guessing since the row movement for this table is disabled, the rowid is safe to use. But I wanted your opinion on this one.

Thanks in advance.
Tom Kyte

Followup  

December 31, 2009 - 11:52 am UTC

such tools already exist.

They typically just "diff" schemas (sql developer can do that for example)

rowids can and will be reused, in general, it would not be safe. I don't know what you are doing exactly, so that is all I can say...

January 12, 2010 - 10:07 am UTC

Reviewer: A reader

The purpose is getting the version changes within the same schema. For that I'm saving in a table the metadata for each revision. I'm also saving the user_tab_columns information.

I will try to describe my question.

For example:

Version 0.1:

create table TEST
(
COLUMN1 NUMBER,
COLUMN2 VARCHAR2(20)
)

Version 0.2:

create table TEST
(
COLUMN1 NUMBER,
COLUMN3 VARCHAR2(30)
)

Unless I save the rowid of SYS.COL$, I have no way of knowing if column2 and column3 are the same. There are at least 2 case scenarios. Column2 could be dropped and then Column3 was created, or Column2 could have been renamed and the size was changed.

My question is if the table is no dropped and then recreated, if the rowid for a given column will be always the same.

Hope this help you understand the purpose.
Tom Kyte

Followup  

January 18, 2010 - 12:56 pm UTC

the rowids can and will be reused, yes, definitely, you cannot rely on them at all.


Now that said, if you need this level of detail - I would strongly suggest you read about source code control.

If you just want to bump schemas, you are going to have to *HAVE TO* live with the fact you are able to only create a diff - nothing more, nothing less.

If you want to know what really happened (and why, and more) you NEED source code control, no way around it.

January 19, 2010 - 9:44 am UTC

Reviewer: Manuel Vidigal from Portugal

The purpose of the application I'm trying to develop is to automatically create the scripts we put on our version control software

I know the rowids can and will be reused. But my question is if for a given column that isn't dropped, only changed, will the rowid in sys.col$ change? For example:

create table mamv.teste(column1 number, column2 varchar2(30));

Using the following query to check the rowid:

SELECT c.rowid,
c.name
FROM sys.col$ c,
all_objects a
WHERE c.obj# = a.object_id
AND a.object_name = 'TESTE'
and a.owner = 'MAMV';

--Results
1 AAAAACAABAAATNWAAA COLUMN1
2 AAAAACAABAAATNWAAB COLUMN2

alter table mamv.teste modify (column1 number(5), column2 varchar2(50));

--Results
1 AAAAACAABAAATNWAAA COLUMN1
2 AAAAACAABAAATNWAAB COLUMN2

alter table mamv.teste drop column column2;

--Results
1 AAAAACAABAAATNWAAA COLUMN1


What I try to show in this example is that it seems that unless the column/table is dropped it will have the same rowid. Can you provide me an example where it doesn't?

Kind regards.
Tom Kyte

Followup  

January 19, 2010 - 5:01 pm UTC

... The purpose of the application I'm trying to develop is to automatically create
the scripts we put on our version control software
...

but you cannot get there, you have NO way to tell the difference between

a) drop and create
b) rename

please do not rely on hacking the real dictionary, it will (not might, it will) only lead to heartburn later.

... But my question is if for a given
column that isn't dropped, only changed, will the rowid in sys.col$ change? ...

no one can honestly answer that. Not even the guys that write the kernel. Today the answer might be "yes", tomorrow it might well be "NO, absolutely not". So, it doesn't really matter what the answer is.


And to me, doing source code control AFTER THE FACT - after the ddl has been done is not doing source code control. The developers will not have told you what they are doing, they will not have told you why they are doing it, you do not have the change history - you have no clue "why" and that is what source control is about - maintaining the "why"

you are approaching this wrong, you are trying to put a band-aid on a system developed without any rigor or rules.



... Can you provide me an
example where it doesn't?
...

no, but that doesn't mean anything. And I know if I did - you would just say "oh, but we'll never do that"


Can you provide me with an assurance that Oracle will never change? Unless you can do that - you should not do what you are doing.


there are so many holes in this approach - the most worrisome is "you don't know WHY anything was done" - source code control would be so much chopped off at the legs without this information.

January 20, 2010 - 6:38 am UTC

Reviewer: Manuel Vidigal from Portugal

Thanks for the information Tom.

Although the purpose is to automatically create the scripts, the goal is not to add them to the source control without developers eyes. The goal is to help us on the creation, of course the developer needs to check the scripts and validate if everything is ok.

--
please do not rely on hacking the real dictionary, it will (not might, it will) only lead to heartburn later.
--

I won't.


Thanks for your help.
Tom Kyte

Followup  

January 20, 2010 - 11:38 am UTC

the developers must have already developed these scripts.

The developers must have already had these scripts code reviewed

the developers must have already added their commentary and documentation on this


This is not needed therefore, unless the developers just willy nilly do whatever they feel like on their own without any sort of review or consensus.

and if they do that, the probability they'll remember why a change was made 2 months later is nill.

Back to square one - you need to do it right - which means YOU do not do it, THEY (the developers) do it - from the beginning, from day one.



Update/delete using ROWID

January 27, 2010 - 3:32 am UTC

Reviewer: Kavita from Delhi India

Hi Tom,

The table I want to update doesn't have primary key. So is it safe to use ROWID to identify the row and perform the appropriate operation
Tom Kyte

Followup  

January 29, 2010 - 3:07 pm UTC

it depends.

If you are stateful (eg: typically NOT web based) you can safely select rowid, .... for update - locking the row, work with the row, and then update it. Yes. Because you KNOW that rowid will not change, you know that rowid won't become the rowid of some other row.


If however, you

a) select the rowid out
b) commit or do not lock the row - eg: lose your state
c) then update the row by rowid.

There is nothing saying you will have updated the SAME row - it could be some other row, the row you are looking for might have another rowid, the row you are looking for might be gone.


primary keys, sort of an important thing.


The rowid will probably work 99% of the time or even more. That is the good news.

The bad news is - sometimes you will update the wrong row, or just lose the row altogether.


The bad news is pretty bad.

Two Tables indenpent

February 01, 2010 - 9:48 pm UTC

Reviewer: Arturo Caceres Sequeira from Nicaragua, Managua

I have 2 tables, at beginin this f1 master and f2 detail, but when enduser insert rows he change restrictions 1 to 1, and confuse repeats no. of id, 1,1 type 1,
1,1 type 2, and i practical the conection of rows lost, but belive using rownum sequence of row1 of f1 is equal row1 of f2, i need put another column to establish relations again.

if not explain good sorry, i speak spanish.
muchas Thanks Tom
Tom Kyte

Followup  

February 02, 2010 - 12:13 pm UTC

sorry, I did not follow this.


"Is it safe to use ROWID to locate a row?", version 9.2.0

April 30, 2010 - 7:28 am UTC

Reviewer: sunny gambhir from DELHI,INDIA

you said 'rowid may be duplicated for two different tables but not for same table'
But rowid id a physical address , so how can rows in different tables share same physical address
Tom Kyte

Followup  

April 30, 2010 - 8:51 am UTC

a rowid is a physical address for a row in an object. Not for a row in just any object.

We have clustered tables whereby more than one tables data may be stored in a block.

ops$tkyte%ORA11GR2> create cluster emp_dept_cluster
  2  ( deptno number(5) )
  3  size 1024
  4  /

Cluster created.

ops$tkyte%ORA11GR2> create index emp_dept_cluster_idx
  2  on cluster emp_dept_cluster
  3  /

Index created.

ops$tkyte%ORA11GR2> create table dept
  2  ( deptno number(5) primary key,
  3    dname  varchar2(14),
  4    loc    varchar2(13)
  5  )
  6  cluster emp_dept_cluster(deptno)
  7  /

Table created.

ops$tkyte%ORA11GR2> create table emp
  2  ( empno    number primary key,
  3    ename    varchar2(10), job      varchar2(9),
  4    mgr      number,       hiredate date,
  5    sal      number,       comm     number,
  6    deptno   number(5) references dept(deptno)
  7  )
  8  cluster emp_dept_cluster(deptno);

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into dept (deptno, dname, loc )
  2  select rownum,
  3         substr( object_name, 1, dbms_random.value( 5, 14 ) ),
  4         substr( owner, 1, dbms_random.value( 8, 13 ) )
  5    from all_objects
  6   where rownum <= 10000
  7  /

10000 rows created.

ops$tkyte%ORA11GR2> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno )
  2  select object_id empno,
  3         substr(object_name,1,10) ename, substr(object_name,1,9) job,
  4         object_id mgr, created hiredate, data_object_id sal,
  5         data_object_id comm,
  6         mod(rownum,10000)+1 deptno
  7    from all_objects
  8  /

72249 rows created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select emp.rowid, dept.rowid,
  2         dbms_rowid.rowid_block_number( emp.rowid ) erid,
  3         dbms_rowid.rowid_block_number( dept.rowid ) drid
  4    from emp, dept
  5   where emp.deptno = dept.deptno
  6     and dept.deptno = 10
  7  /

ROWID              ROWID                    ERID       DRID
------------------ ------------------ ---------- ----------
AAAYUzAAEAABIjLAAC AAAYUzAAEAABIjLAAC     297163     297163
AAAYUzAAEAABIjLAAJ AAAYUzAAEAABIjLAAC     297163     297163
AAAYUzAAEAABIjLAAQ AAAYUzAAEAABIjLAAC     297163     297163
AAAYUzAAEAABIjLAAX AAAYUzAAEAABIjLAAC     297163     297163
AAAYUzAAEAABIjLAAe AAAYUzAAEAABIjLAAC     297163     297163
AAAYUzAAEAABIjLAAl AAAYUzAAEAABIjLAAC     297163     297163
AAAYUzAAEAABIjLAAs AAAYUzAAEAABIjLAAC     297163     297163
AAAYUzAAEAABIjLAAz AAAYUzAAEAABIjLAAC     297163     297163

8 rows selected.

<b>they are all on the same block - that is the goal of the btree cluster, to pre-join data... Note the first two rowids - they are the same.  The rowid is a file/block/row slot on block - GIVEN AN OBJECT to retrieve.  </b>

ops$tkyte%ORA11GR2> select count(distinct rid) , count(*)
  2    from (select rowid rid from dept
  3           union all
  4          select rowid rid from emp)
  5  /

COUNT(DISTINCTRID)   COUNT(*)
------------------ ----------
             72249      82249


See how there are only 72,249 distinct rowids for 82,249 rows....

April 30, 2010 - 9:15 am UTC

Reviewer: Anto

And rows belonging to 2 different tables cannot have the same rowid(except for clustering as you have demonstrated above) - right ?
Tom Kyte

Followup  

April 30, 2010 - 9:21 am UTC

you need to have two rows from two different tables be on the same block for that to happen, and the only way I know for that to be true would be in a cluster (hash or b*tree)

but the rule is - rowids are only unique within the context of an object.

April 30, 2010 - 9:19 am UTC

Reviewer: A reader

you said 'rowid may be duplicated for two different tables but not for same table'. ---> does not mean that 2 different tables can have the same rowid(except in the case of clustering) - otherwise one can never update based on rowids

Tom Kyte

Followup  

April 30, 2010 - 9:22 am UTC

Just remember this:

rowids are not unique in a database, rowids are only unique within and object.

That is what you need to know - not the exceptions.

What I'm worried is, people are asking just so they can go back to pretending that rowids are unique in a database. They aren't and if you ever make that assumption, you will have a bug in your code just waiting to happen.


... otherwise one can never update based
on rowids

...

I don't know why you would jump to that conclusion. If you have a table_name PLUS rowid, you have a unique key.

April 30, 2010 - 11:53 am UTC

Reviewer: A reader


when does the column add or alter

July 11, 2010 - 7:49 pm UTC

Reviewer: Yao from Dalian . LIaon China

Hi Tom
The first question: somebody ask me when does a column of a table add or alter but i don't know ...please tell me which data directory view can view the date ?
The second question: 11g tells me i can create a trigger with order,so how to write it ?
with kind regards.
Tom Kyte

Followup  

July 19, 2010 - 8:58 am UTC

we do not track that minutiae. You would have to set up your own auditing if you wanted that. We usually use configuration management software for things like that (you know - documentation - so you cannot only see WHEN something took place but more importantly - infinitely more importantly - WHY something took place)


Yes you can order the triggering firing order in 11g - but you know what - if you feel the need to - I know for a fact you have a bad design. You should (in order)

a) not use a trigger at all.
b) if you ignore (a), put your code in a package, have the main procedure of the package call the routines in the right order and have your trigger be all of one line long - it just calls the main package procedure that does the right thing.

ordering of triggers:
http://docs.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#g1041767

Using rowid with outer join

June 04, 2012 - 3:39 am UTC

Reviewer: Srastanka from India

Is there any risk in using rowid along with outer joins as in the query below? It is known that neither the table table1 nor table2 has a primary key.
It is also known that row movement is disabled and there is going to be only one session which is going to operate.

UPDATE (SELECT t1.col1, t2.col2
FROM table1 t1
LEFT OUTER JOIN
table2 t2
ON t1.some_column = t2.some_column) tt
SET tt.col1 = tt.col2
WHERE t1.rowid IN (<set of rowids>);
Tom Kyte

Followup  

June 04, 2012 - 9:11 am UTC

where is the set of rowids coming from?

Using rowid with outer join

June 05, 2012 - 6:23 am UTC

Reviewer: Srastanka from India

Lets say the set of rowids come from a subquery like

UPDATE (SELECT t1.col1, t2.col2
FROM table1 t1
LEFT OUTER JOIN
table2 t2
ON t1.some_column = t2.some_column) tt
SET tt.col1 = tt.col2
WHERE table1.rowid IN (SELECT t3.rowid FROM table1 t3, table4 t4 WHERE t3.col3 = t4.col4 AND t3.col3 = '<condition_value>');

Please note that both the aliases t1 and t3 are for table1.

Assuming this query will execute fine, but will it update those records of table1 that we intend to? And is there any risk involved?
Tom Kyte

Followup  

June 05, 2012 - 1:03 pm UTC

given that multiversioning is a basic tenent of the Oracle database - that'll work, the rowids will point to rows that exist and are the same rows you selected.


but you should move the where clause into the SELECT component, it shouldn't be "outside" like that.

why rowid+pk?

June 08, 2012 - 1:19 am UTC

Reviewer: A reader

Hi Tom,

I can not understand the point 'It is perfectly safe to use the rowid in ALL CASES however, assuming you combine
it with the primary key as well:
'.

I know use only rowid is not a good idea, when we did not get lock of it and concurrently some other session delete and insert new row which reuse this rowid. right?

But what's the point of 'rowid+primary key'?
Why not just use primary key? As primary key already uniqly identify the record. Even some other session delete and re-insert it, we are still considering it as the same/original record,right?
Tom Kyte

Followup  

June 08, 2012 - 7:20 am UTC

the rowid+primary key is being used for updating a row in a "lost update" protection scheme (that is what we were discussing above in the original question)


If the row was modified in a way that caused it to move (the partition key was updated for example), then the update by rowid+pk will not update anything (that is the desired outcome - the row was already modified by someone - we don't want a 'lost update' so the update updates zero rows)

If the row was not modified in a way that caused it to move, then the rowid will find it faster than the primary key.

If the row was deleted and someone inserted a new row that just happened to reuse that rowid - then the rowid will 'find' a row but the primary key will "unfind it"


The primary key would suffice, however, it would require an unique unique scan of an index - so the rowid could offer a performance benefit.

September 26, 2012 - 2:49 pm UTC

Reviewer: A reader


RowID's and Data Migration

October 10, 2012 - 3:02 pm UTC

Reviewer: Nand Kishore Sagi from Raleigh, NC

Hi Tom,

We are currently working on migrating data different two versions of same tool (Argus - Oracle Provided). For each table migration we have two different scripts

(1.) Migration script developed by Dev Engineers
(2.) Validation scripts developed by QA Engineers

For each table there is a common understanding between the development team and the QA team as to how to identify a single row in the table. We cannot alter the schemas and underlying tables to add or delete columns.

The major challenge is always with tables having composite keys. For example a common reoccurring primary key configuration at table level is EmpID and SeqNum (Employee table analogy) where SeqNum is generated at runtime. These SeqNum's always vary across the versions of the systems.

To overcome this challenge we always used a unused column or column containing NULL data to populate the OLD SEQ_NUM's. Once the validation process is done then all the columns used specifically for the migration process would be cleaned up.

I (Developer) have hit across a table that does not a primary key at table level. No combination of columns gives me a unique set of records. I was thinking along the lines of dumping the RowID in one of the varchar columns and clean it up after migration. When the migration process is going on there would not be any activity on the old system (So no DML's). Will this RowID help the QA team in identifying the row of data which should be validated. The QA team would be basically picking this RowID from the new system and then go to old system and pick up the row and validate ?

Thanks a lot for patiently reading this lengthy post and as always for sharing your wisdom.

Thanks and Regards
Nand Kishore Sagi
Tom Kyte

Followup  

October 10, 2012 - 4:39 pm UTC

as long as you do not have row movement enabled on a table - the rowid assigned to a row when inserted will remain its rowid until it is deleted.

so yes, it would work - we ourselves use rowids in our online redefinition if you have no primary key.

just do not allow for row movement on the source table.

partition key

March 15, 2013 - 12:10 pm UTC

Reviewer: kiran

Hi Tom,

What is the partition key we are referring here? Let's suppose table t is partitioned by tab_id and primary key is created on tab_id, it is highly unlikely(for ex., none) that tab_id is updated in this table. In this scenario, is it ok to use rowid's to update this table?

Thank you
Tom Kyte

Followup  

March 25, 2013 - 10:06 am UTC

I don't know what partition key you are thinking about - this is a big page, hasn't been updated in many months, the review was not about partitioning. So I really have no idea what you are referring to...


If row movement is not enabled, and therefore updating tab_id could never move a row, and since you say tab_id is never modified anything - then the rowid assigned to a row upon insert is the rowid the row will have *forever* (unless you rebuild the table - using DDL like alter table t move, or dbms_redefintion).



If you know that row movement is not enabled, and if you know that the table will never be online reorganized while you are using it, then using rowid by itself is "safe"

Row ID manipulation in a table

November 04, 2014 - 9:31 am UTC

Reviewer: Boobal Ganesan from India

Hi Tom,

I have a clarification with the rowid in a table.

If I have a rowid with AAABBBCCC for a row in a table and if i delete it followed by a commit, is there any possibility of the same rowid AAABBBCCC to appear again for some other row insertion of the same table or any other table?

rowid and replication

November 12, 2014 - 12:32 am UTC

Reviewer: Scott Duncan from UK

Hi, I have written a mechanism that deletes unwanted old data from a large Oracle database, large table e.g. 750M rows and I need to delete 400M according to a large where clause condition to check whether the columns are les than an allowed tolerance, and I found that it performs best if I query the rows to be deleted by rowid, order by the rowid and use it for the deletion. This works well, however the whole database, including redo logs etc.. are block replicated to a Disaster Recovery system. I am wondering whether it is safe/ok in Oracle 10g and 11g to do this as I previously understood that the rowid's were only valid within the database that they are derived? If you can possibly clarify whether this additional consideration for using rowid is ok.

Many thanks in anticipation of any help/advice you can possibly offer about this.

Scott

Rowid for deleting millions of records using bul collect

December 09, 2015 - 9:11 pm UTC

Reviewer: Shefali from New York

I used rowed as the key to delete around 19 million records in a table which already has a unique index using bulk collections . I am committing the delete after every 300000 rows.

Which approach will be faster.. ? Using Rowid for deleting the records or using Unique index key for deleting the records.

Please suggest.

Thanks.
Connor McDonald

Followup  

December 10, 2015 - 2:07 pm UTC

How many rows do you have left ?

If 19million is a large percentage of your table, it might be better not to delete, but to create a new table just keeping the ones you want to retain.


What if my table doesnt have primary key ?

April 23, 2018 - 11:57 am UTC

Reviewer: Suman Panigrahi from India

I want to bulk update my table for all rows in a column, but it doesnt have primary key. How to do it ?
Chris Saxon

Followup  

April 23, 2018 - 3:08 pm UTC

Why does that matter? And what's that got to do with the original question?