首页 > Linux操作系统 > Linux操作系统 > 对表的shrink都做了些什么
SQL> truncate table rate;
Table truncated.
SQL> insert into rate values('a',1);
1 row created.
SQL> insert into rate values('b',2);
1 row created.
SQL> insert into rate values('c',3);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number(rowid),rate from rate order by rate;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) RATE
------------------------------------ ----------
50 1
51 2
52 3
SQL> select rowid,rate from rate order by rate;
ROWID RATE
------------------ ----------
AAAD9dAAGAAAAAyAAA 1
AAAD9dAAGAAAAAzAAA 2
AAAD9dAAGAAAAA0AAA 3
SQL> select object_id,object_name,object_type from user_objects where object_name='IDX_RATE'
2 /
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ---------------------------------------- -------------------
16211 IDX_RATE INDEX
SQL> delete from rate where rate=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter table rate shrink space;
Table altered.
SQL> select dbms_rowid.rowid_block_number(rowid),rate from rate order by rate;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) RATE
------------------------------------ ----------
38 1
37 3
SQL> select rowid,rate from rate order by rate;
ROWID RATE
------------------ ----------
AAAD9dAAGAAAAAmAAA 1
AAAD9dAAGAAAAAlAAA 3
---可以发现,执行shrink之后,数据所在的block和rowid都发生了变化,在shrink前后,对相应的索引数据块做dump,查看索引中数据是否也相应的被更新,
SQL> alter session set events 'immediate trace name treedump level 16211';
Session altered.
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(25165925),
2 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(25165925)
3 from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(25165925) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(25165925)
---------------------------------------------- -----------------------------------------------
6 101
SQL> alter system dump datafile 6 block 101;
System altered.
SQL> alter session set events 'immediate trace name treedump level 16211';
Session altered.
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(25165925),
2 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(25165925)
3 from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(25165925) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(25165925)
---------------------------------------------- -----------------------------------------------
6 101
SQL> alter system dump datafile 6 block 101;
System altered.
row#0[1876] flag: ------, lock: 2, len=12 row#0[1828] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 02 col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 80 00 32 00 00 col 1; len 6; (6): 01 80 00 26 00 00
row#1[1864] flag: ------, lock: 2, len=12 row#1[1876] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 03 col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 80 00 33 00 00 col 1; len 6; (6): 01 80 00 32 00 00
row#2[1852] flag: ------, lock: 2, len=12 row#2[1840] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04 col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 80 00 34 00 00 col 1; len 6; (6): 01 80 00 25 00 00
----- end of leaf block dump ----- ----- end of leaf block dump -----
可以很清晰的看出,索引块里的数据发生了变化,这说明在shrink的同时,索引数据块里的数据也将被同步更新。
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/10972173/viewspace-662426/ ,如需转载,请注明出处,否则将追究法律责任。