CODEWITHSUNDEEP
sqloracle-databaseoracle11g
learner
learner

Reputation: 1023

Join tables on Oracle ROWID

I have 2 tables A and B in Oracle 11g. Table B holds the exact same data as Table A but with additional ROWID column which the ROWID of Table A.

I am going to change values of a column say colx in Table B. And then update colx in table A by joining table A and table B with the ROWID's.

Now, ROWID is encoded. Should I convert the ROWID to char ? How do I do a join between table A and table B when one is a column and the other is Oracle ROWID pseudocolumn.

UPDATE tablea ta
   SET (colx) = (SELECT colx FROM tableb tb WHERE ta.rowid = tb.rowid)
 WHERE EXISTS (
    SELECT 1
      FROM tableb tb
     WHERE ta.rowid = tb.rowid )

Upvotes: 0

Views: 1011

Answers (2)

pmdba
pmdba

Reputation: 7033

ROWID is a pseudo-column that represents the data's physical location in a data block. It is NOT constant, and should not be used for any referential integrity or joins.

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.

ROWID should only be used - if ever - in combination with a true primary key. It should never be used as a primary key or instead of a true primary key.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53140678334596

Upvotes: 1

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Now, ROWID is encoded.

DOn't you use standard oracle type ROWID? If - yes, you don't need to convert it. For example, your table B has column TAB_A_ROWID which contains rowid of related record in table A. So you can easily join them:

select *
from A join B on B.TAB_A_ROWID = A.rowid

Btw, in you code I see "ta.rowid = tb.rowid"

UPDATE tablea ta
  SET (colx) = (SELECT colx FROM tableb tb WHERE ta.rowid = tb.rowid)

but rowid is pseudocolumn, You can't add column rowid. so you specified both of them - real rowids, not from own column.

Upvotes: 1

Related Questions