ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE ROW 的插入

ORACLE ROW 的插入

原创 Linux操作系统 作者:ysp_1984 时间:2009-05-11 20:57:06 0 删除 编辑


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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: SYSDBA、SYSOPER权限
请登录后发表评论 登录
全部评论

注册时间:2009-05-08

  • 博文量
    15
  • 访问量
    18404