Oracle rowid


一：rowid组成

https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF00213

https://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns008.htm#SQLRF00254

(1)The data object number of the object

(2)The data block in the data file in which the row resides

(3)The position of the row in the data block (first row is 0)

(4)The data file in which the row resides (first file is 1). The file number is relative to the tablespace.


二：rowid重要用途

https://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns008.htm#SQLRF00254

Rowid values have several important uses:

(1)They are the fastest way to access a single row.

(2)They can show you how the rows in a table are stored.

(3)They are unique identifiers for rows in a table.


三：rowid限制

Small Datafile

Max(一个表空间的数据文件数)=(2^10)-1=1023

Max(一个数据文件包含块数)=2^22=4194304=4M(block)

Max(一个block包含行数)=2^16=65536=64k(rows)

Max(一个数据库内object个数)=2^32=4294967296=4G(objects)

Bigfile Datafile

Max(一个表空间的数据文件数)=1

Max(一个数据文件包含块数)=2^(22+10)=4294967296=4G(block)

Max(一个block包含行数)=2^16=65536=64k(rows)

Max(一个数据库内object个数)=2^32=4294967296=4G(objects)


测试如下：

(1)创建测试数据

SQL> createtablespacechenjch_tbs datafile'/u01/app/oracle/oradata/cc/chenjch_tbs01.dbf'size10M autoextendonmaxsize1G;

SQL> createuserchenjch identifiedbya defaulttablespacechenjch_tbs;

SQL> grantconnect,resource,dbatochenjch;

SQL> createtablet1 asselect*fromscott.emp;

SQL> createtablet2 asselect*fromscott.dept;

(2)查看t1表rowid信息

SQL> SELECTrowid,empno fromt1 a orderbyempno;

SQL>

selectsubstr(rowid,1,6)"object",

substr(rowid,7,3)"file",

substr(rowid,10,6)"block",

substr(rowid,16,3)"row"

fromt1;
---取出任意一行rowid

AAAVV9AAFAAAACDAAC
---通过rowid计算对应的obj#,rfile#,block#,row#;

(1)obj#=AAAVV9=21*64^2+21*64+61=87421

(2)rfile#=AAF=5

(3)block#=AAAACD=2*64+3=131

(4)row#=AAC=2


---也可以通过dbms_rowid转换得到相应的obj#,rfile#,block#,row#;
SQL>

selectdbms_rowid.rowid_object(rowid)object_id,

dbms_rowid.rowid_relative_fno(rowid)file_id,

dbms_rowid.rowid_block_number(rowid)block_id,

dbms_rowid.rowid_row_number(rowid)row_number,

ROWID,

empno

fromT1

orderbyempno;


(3)rowid和限制

Small Datafile

rowid采用10个byte来存储=8*10=80bit，

其中:

obj#占用32bit；

rfile#占用10bit；

block#占用22bit；

row#占用16bit。

Max(一个表空间的数据文件数)=(2^10)-1=1023

Max(一个数据文件包含块数)=2^22=41943044=4M(block)

Max(一个block包含行数)=2^16=65536=64k(rows)

Max(一个数据库内object个数)=2^32=4294967296=4G(objects)


Bigfile Datafile

rowid采用10个byte来存储=8*10=80bit，

其中:

obj#占用32bit；

rfile#占用0bit；

block#占用32bit；

row#占用16bit。

Max(一个表空间的数据文件数)=2^0=1

Max(一个block包含行数)=2^16=65536=64k(rows)

Max(一个数据库内object个数)=2^32=4294967296=4G(objects)

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！