SQL> select utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A')))
from t where id in(1, 2, 500, 501);
------------------------------------
000000
000001
0001F3
0001F4可以看出这个现实的结果是行数，但是实际上这个是十六进制的方式。沃恩需要再这个基础上进一步转换。
SQL> select to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
from t where id in(1, 2, 500, 501);
ROWSLOT
----------
0
1
499
500
如此一来，整个过程是清晰了很多，那么这个说法到底是否靠谱呢。
我们可以使用它来得到和dbms_rowid同样的效果。
select rowid as therowid, id,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid,1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
from t where id <= 2 ;
THEROWID ID OBJID FILENUM BLOCKNUM ROWSLOT
------------------ ---------- ---------- ---------- ---------- ----------
AAAVs+AABAAAXHJAAA 1 88894 1 94665 0
AAAVs+AABAAAXHJAAB 2 88894 1 94665
所以说如此一来整个ROWID的实现方式就一目了然了，而在这个测试中如果结合ROWNUM其实也就更有意思了。我们后续来揉在一起来对比一下。