ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 对表的shrink都做了些什么

对表的shrink都做了些什么

原创 Linux操作系统 作者:lsq_008 时间:2010-05-11 14:10:49 0 删除 编辑

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

下一篇: execute和fetch
请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    335
  • 访问量
    1369143