IDS11.5新功能--通过sysadmin删除无数据的表区段

最新推荐文章于 2021-10-16 13:54:13 发布

liaosnet

最新推荐文章于 2021-10-16 13:54:13 发布

阅读量253

点赞数

本文链接：https://blog.csdn.net/liaosnet/article/details/105766817

版权

Informix 11.50在xC4版本后增加的新功能:
1,将表中的数据移动到表前区未用的空间上(如,表记录删除后的留下的空间);
2,将表末尾没有数据的空间从表中删除;
通过以上2个功能,可以实现回收表未用空间给其它表使用.

以下通过示例详述:
1,建立一个表t1导入一定的数据,并通过建立t2,t3,t4,t5表将表t1的区段分离.

informix@suse10:/opt/informix/tmp/tmp> dbaccess testdb -

> create table t1(id char(10),name char(20));

Table created.

> create table t2(id char(1));

Table created.

> load from t1.unl delimiter " " insert into t1;

10000 row(s) loaded.

> create table t3(id char(1));

Table created.

> load from t1.unl delimiter " " insert into t1;

10000 row(s) loaded.

> create table t4(id char(1));

Table created.

> load from t1.unl delimiter " " insert into t1;

10000 row(s) loaded.

> create table t5(id char(1));

Table created.

2,检查下表t1的区段情况.可以发现,由于t2,t3,t4表的原因,t1生成了4个区段.

informix@suse10:/opt/informix/tmp/tmp> oncheck -pt testdb:t1

TBLspace Report for testdb:informix.t1

    Physical Address               3:513
    Creation date                  03/03/2010 17:20:14
    TBLspace Flags                 801        Page Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               30
    Number of special columns      0
    Number of keys                 0
    Number of extents              4
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8
    Next extent size               8
    Number of pages allocated      512
    Number of pages used           510
    Number of data pages           509
    Number of rows                 30000
    Partition partnum              3145790
    Partition lockid               3145790

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0            3:1223           8          8
                    8            3:1239         168        168
                  176            3:1415         168        168
                  344            3:1591         168        168

3,然后删除t1表中的一些数据.那样表中的将有些数据页将变成不可用了.

informix@suse10:/opt/informix/tmp/tmp> dbaccess testdb -

Database selected.

> delete from t1 where id < '8000';

23343 row(s) deleted.

> select count(*) from t1;

      (count(*))

            6657

1 row(s) retrieved.

informix@suse10:/opt/informix/tmp/tmp> oncheck -pt testdb:t1

TBLspace Report for testdb:informix.t1

    Physical Address               3:513
    Creation date                  03/03/2010 17:20:14
    TBLspace Flags                 801        Page Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               30
    Number of special columns      0
    Number of keys                 0
    Number of extents              4
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8
    Next extent size               8
    Number of pages allocated      512
    Number of pages used           510
    Number of data pages           122
    Number of rows                 6657
    Partition partnum              3145790
    Partition lockid               3145790

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0            3:1223           8          8
                    8            3:1239         168        168
                  176            3:1415         168        168
                  344            3:1591         168        168

4,通过sysadmin库的task将表中数据前移
命令格式为: execute function task("table repack","tabname","dbname");

informix@suse10:/opt/informix/tmp/tmp> dbaccess sysadmin -

Database selected.

> execute function task("table repack","t1","testdb");

(expression) Succeeded: table repack testdb:informix.t1

1 row(s) retrieved.

5,通过sysadmin库的task删除没有数据的数据空间
命令格式为: execute function task("table shrink","tabname","dbname");

informix@suse10:/opt/informix/tmp/tmp> dbaccess sysadmin -

Database selected.

> execute function task("table shrink","t1","testdb");

(expression) Succeeded: table shrink testdb:informix.t1

1 row(s) retrieved.

6,查看当前表t1的区段情况,没有数据的表区段已经删除

informix@suse10:/opt/informix/tmp/tmp> oncheck -pt testdb:t1

TBLspace Report for testdb:informix.t1

    Physical Address               3:513
    Creation date                  03/03/2010 17:20:14
    TBLspace Flags                 801        Page Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size               30
    Number of special columns      0
    Number of keys                 0
    Number of extents              2
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8
    Next extent size               8
    Number of pages allocated      114
    Number of pages used           114
    Number of data pages           113
    Number of rows                 6657
    Partition partnum              3145790
    Partition lockid               3145790

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0            3:1223           8          8
                    8            3:1239         106        106

通过oncheck -pe查看数据空间上的表分布

testdb:'informix'.t1        1223        8
testdb:'informix'.t2            1231        8
testdb:'informix'.t1          1239      106
FREE                           1345       62   --原区段168,回收未用的62
testdb:'informix'.t3            1407        8
FREE                  1415      168   --全回收
testdb:'informix'.t4          1583        8
FREE                1591      168
testdb:'informix'.t5          1759        8
FREE                            1767    98233

至此,就完成了表未用空间的回收.