1

Oracle ROWID is said to be unique within a table. I didn't understand how can two rows of two different tables stored in the same cluster have the same rowid since the last three characters of the ROWID format indicate the row number in the database block?

I understand that rows in the clustered tables are persisted in the same database block but since ROWID includes row number, shouldn't that distinguish between two rows in the database block?

Andy Dufresne
  • 6,022
  • 7
  • 63
  • 113

2 Answers2

1

Is simple. Because "friendly" rows of the clustered table are stored together in the block, that means you can have the same rowid.

In the picture below (from frontiernet) first dept row has rownumber 1 and first emp row have rownumber 1. This is first occurence.

After this we have emp 2, emp 3... After all emps in this dept, if there is enough space, you'have row two for dept but his rowid was encountered for emp 2 in previous dept.

And so on.

This happens because the row number from a rowid is relative to its table and current block.

picture

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
  • Ok. If I understand it correctly, you mean since dept and emp column values are stored in the same "row piece" they both would end up having the same row id? Clustering nests the EMP_Table within the DEPT_Table, whose primary key is also the Cluster Key. – Andy Dufresne Sep 20 '11 at 17:41
  • You are close. The question is what means row number (the last three characters from rowid.).If it means the nth row of table x in this block, my explanation upside is valid. – Florin Ghita Sep 20 '11 at 18:56
  • I do not understand the reason you say "you are close". Aren't rows of the emp and dept tables not stored by the clustered key which is dept_no and hence they end up having the same row id? Knowing how do two rows in different tables part of a cluster have the same rowid is the main reason of posting the above question. – Andy Dufresne Sep 21 '11 at 16:43
  • yes it is correct, but for one row of dept you have many rows of emp. So, only few have same rowids, not all of them :) Right? – Florin Ghita Sep 22 '11 at 11:07
  • yes sounds right :). It would have been better to have a confirmation from an Oracle DBA. – Andy Dufresne Sep 22 '11 at 12:44
0

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

checkout: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns008.htm

Janco
  • 1,130
  • 1
  • 8
  • 15