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

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

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