delete未提交rollback是否会改变行号

来源：互联网发布：java 小红本编辑：程序博客网时间：2024/06/05 16:06

今天做个试验，delete未提交rollback是否会改变行号？

创建测试表：create table dxmy (id number,name varchar2(100));插入数据insert into dxmy values(1,'dxmy');insert into dxmy values(2,'dxmy');insert into dxmy values(3,'dxmy');insert into dxmy values(4,'dxmy');commit;查看行号SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,  2         dbms_rowid.rowid_block_number(rowid) block_id,  3         dbms_rowid.rowid_row_number(rowid) row_id,  4         id  5    from dxmy;        FNO   BLOCK_ID     ROW_ID         ID---------- ---------- ---------- ----------         4        462          0          1         4        462          1          2         4        462          2          3         4        462          3          4 delete，但是不提交：SQL> delete dxmy where id = 1; 1 row deleted SQL> SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,  2         dbms_rowid.rowid_block_number(rowid) block_id,  3         dbms_rowid.rowid_row_number(rowid) row_id,  4         id  5    from dxmy;        FNO   BLOCK_ID     ROW_ID         ID---------- ---------- ---------- ----------         4        462          1          2         4        462          2          3         4        462          3          4rollback：SQL> rollback; Rollback completeSQL> select dbms_rowid.rowid_relative_fno(rowid) fno,  2         dbms_rowid.rowid_block_number(rowid) block_id,  3         dbms_rowid.rowid_row_number(rowid) row_id,  4         id  5    from dxmy;        FNO   BLOCK_ID     ROW_ID         ID---------- ---------- ---------- ----------         4        462          0          1         4        462          1          2         4        462          2          3         4        462          3          4行号未改变。那如果delete之后，我提交了，然后重新插入呢？SQL> delete dxmy where id = 1; 1 row deleted SQL> commit; Commit completeSQL> insert into dxmy values(1,'dxmy'); 1 row inserted SQL> commit; Commit completeSQL> select dbms_rowid.rowid_relative_fno(rowid) fno,  2         dbms_rowid.rowid_block_number(rowid) block_id,  3         dbms_rowid.rowid_row_number(rowid) row_id,  4         id  5    from dxmy;        FNO   BLOCK_ID     ROW_ID         ID---------- ---------- ---------- ----------         4        462          1          2         4        462          2          3         4        462          3          4         4        463          0          1<pre name="code" class="html">SQL> delete dxmy where id = 1; 1 row deleted SQL> commit; Commit complete

SQL> insert into dxmy values(1,'dxmy2');

1 row inserted

SQL> commit;

Commit complete

SQL>
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,
2         dbms_rowid.rowid_block_number(rowid) block_id,
3         dbms_rowid.rowid_row_number(rowid) row_id,
4         id
5    from dxmy;

       FNO   BLOCK_ID     ROW_ID         ID
---------- ---------- ---------- ----------
         4        462          1          2
         4        462          2          3
         4        462          3          4
         4        463          1          1

然后就发现：
1.delete未提交即rollback不会改变行号
2.delete提交后插入数据，可能会改变行号，但是也有可能不改变，但行的位置肯定会改变。

0 0