1、创建一个测试表
SQL> create table test(id int,name varchar2(4000) default lpad('*',3000,'*')) tablespace data;
Table created.
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
如果我们没有提供NAME列的值,默认情况下每个块只能插入2行,到奇数的时候将会重新分配一个块(前提是我们不提供NAME列)
2、往表里插入3行数据,但不提供NAME列的值
SQL> insert into test(id) values(1);
1 row created.
查看改行的信息
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 0 1 3000
继续插入一行
SQL> insert into test(id) values(2);
1 row created.
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 0 1 3000
6 47 1 2 3000
假如我们再插入一行,由于块47的空间已不够用,ORACLE将会使用新的块。
SQL> insert into test(id) values(3);
1 row created.
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 0 1 3000
6 47 1 2 3000
6 48 0 3 3000
3、删除ID=2的行,然后重新插入一行,ORACLE是优先使用块47还是48?
SQL> delete test where id=2;
1 row deleted.
SQL> insert into test(id) values(22222);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 0 1 3000
6 47 2 22222 3000
6 48 0 3 3000
反复测试了一下,感觉ORACLE一直优先使用的块47.
4、然后我们以下面方式插入2行数据
SQL> insert into test values(4,'test');
1 row created.
SQL> insert into test values(5,'test');
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 0 1 3000
6 47 1 2 3000
6 47 2 4 4
6 47 3 5 4
6 48 0 3 3000
由于块47里还有剩余的空间,只要还没达到1-pctfree 我们就还可以利用这些剩余空间。
现在块47里有4行,大小约为6008个字节。
此时我们用下面的语句更新第4,5行的NAME列。
SQL> update test set name=lpad('*',3000,'*') where id in (4,5);
2 rows updated.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 3 5 3000
6 47 0 1 3000
6 47 1 2 3000
6 48 0 3 3000
6 47 2 4 3000
由于一个块是8K,但我们的块47好像已经超过8K了(大约12000) ,很显然这4行数据的NAME列的数据不可能存在一个块里,只不过行头信息还在块47里。但NAME列其实已经在别的块里了。
接着我做了下面的测试:
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 3 5 3000
6 44 1 6 3000
6 45 0 7 3000
6 45 1 8 3000
6 46 0 9 3000
6 46 1 10 3000
6 47 0 1 3000
6 47 1 2 3000
6 48 0 3 3000
6 47 2 4 3000
6 49 0 11 3000
11 rows selected.
BLOCK 49是新分配的一个块,里面只有一条数据。
接着我们插入下面2条数据
SQL> insert into test values(12,'test');
1 row created.
SQL> insert into test values(13,'test');
1 row created.
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 3 5 3000
6 44 1 6 3000
6 45 0 7 3000
6 45 1 8 3000
6 46 0 9 3000
6 46 1 10 3000
6 47 0 1 3000
6 47 1 2 3000
6 48 0 3 3000
6 47 2 4 3000
6 49 0 11 3000
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 49 1 12 4
6 49 2 13 4
13 rows selected.
BLOCK 49里有3条数据了
执行下面的更新操作
SQL> update test set name=lpad('*',3000,'*') where id in (12,13);
2 rows updated.
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 3 5 3000
6 44 1 6 3000
6 45 0 7 3000
6 45 1 8 3000
6 46 0 9 3000
6 46 1 10 3000
6 47 0 1 3000
6 47 1 2 3000
6 48 0 3 3000
6 47 2 4 3000
6 49 0 11 3000
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 49 1 12 3000
6 49 2 13 3000
13 rows selected.
如果前面的块里还有剩余空间可以容纳第13行NAME列的数据的话,则NAME列的数据将会存在该块里。否则将会重新分配一个块 譬如块50。
在插入2行数据
SQL> insert into test(id) values(14);
1 row created.
SQL> insert into test(id) values(15);
1 row created.
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 3 5 3000
6 44 1 6 3000
6 45 0 7 3000
6 45 1 8 3000
6 46 0 9 3000
6 46 1 10 3000
6 47 0 1 3000
6 47 1 2 3000
6 48 0 3 3000
6 47 2 4 3000
6 49 0 11 3000
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 49 1 12 3000
6 49 2 13 3000
6 50 1 14 3000
6 51 0 15 3000
15 rows selected.
本来块50可以存2条数据的 ,可是由于第13行的NAME列的数据已经占据了一部分空间,使得块50只能存一条了。
继续插入数据 块51由于没有别的行占用其空间 所以它应该能存2条数据。看看结果如何:
SQL> insert into test(id) values(16);
1 row created.
SQL> select dbms_rowid.rowid_relative_fno(a.rowid) file#,dbms_rowid.rowid_block_number(a.rowid) block#, dbms_rowid.rowid_row_number(a.rowid) row#,a.id,length(a.name) from test a;
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 47 3 5 3000
6 44 1 6 3000
6 45 0 7 3000
6 45 1 8 3000
6 46 0 9 3000
6 46 1 10 3000
6 47 0 1 3000
6 47 1 2 3000
6 48 0 3 3000
6 47 2 4 3000
6 49 0 11 3000
FILE# BLOCK# ROW# ID LENGTH(A.NAME)
---------- ---------- ---------- ---------- --------------
6 49 1 12 3000
6 49 2 13 3000
6 50 1 14 3000
6 51 0 15 3000
6 51 1 16 3000
16 rows selected.
对于直接路径插入,不管前面的块是否有空闲空间,ORACLE都会重新分配一个块。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16162908/viewspace-594770/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16162908/viewspace-594770/