I finally found time to get back to External Tables and have a list of blog posts I need to write on this topic. Here’s a brief one.
DBMS_ROWID will nicely break down a heap table’s rowid for you into file number, block number, and row number but it doesn’t handle the rowids coming from External Tables. So let’s look at how to make sense of them. They fall under the datatype UROWID which is a nominally opaque rowid defined by the data source. The first byte of a UROWID tells you which data source it came from and consequently how to deconstruct it.
The easiest way to see what is happening is via the SQL Dump function:
SQL> column xtrowid format a55
SQL> select c_custkey, dump(rowid,16) "XTROWID" from c_et
2> where c_custkey < 10;
C_CUSTKEY XTROWID
---------- -------------------------------------------------------
1 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,1
2 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,2
3 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,3
4 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,4
5 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,5
6 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,6
7 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,7
8 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,8
9 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,9
9 rows selected.
Datatype 208 is ‘internal universal rowid”.
The first byte of a UROWID tells you what kind of UROWID it is:
- physical rowid
- logical rowid
- remote rowid
- external table rowid
- cube rowid
This blog post is only about External Table UROWIDs: the next four bytes tell you the object number, here 0x127a2 is 75682 and we can confirm this simply:
SQL> select OBJ# from SYS.OBJ$ where name = 'C_ET');
OBJ
----------
75682
1 row selected.
The next four are the sequence number, and the last eight bytes are the row number in the sequence as you can see from the monotonically increasing integers. You can think of the sequence number as granules, here we get further into the table just to show the sequence increasing:
SQL> select c_custkey, dump(rowid,16) "XTROWID" from c_et
2 where mod(c_custkey, 100) = 0
3 and c_comment like '%slyly regular request%';
C_CUSTKEY XTROWID
---------- -------------------------------------------------------
9100 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,23,8c
33000 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,80,e8
59100 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,e6,dc
71400 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,1,0,0,0,0,0,0,1f,d5
88700 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,1,0,0,0,0,0,0,63,69
108200 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,1,0,0,0,0,0,0,af,95
113000 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,1,0,0,0,0,0,0,c2,55
131000 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,1,0,0,0,0,0,1,8,a5
8 rows selected.
Hope this helps,
Roger