DB2 列式存储表的空间回收

如果一张表是列式存储的,比如:

CREATE TABLE "DB2TST  "."T0417"  (
                  "ID" INTEGER )   
                 IN "TBS2" 
                 ORGANIZE BY COLUMN;
                 
那么删除完数据之后的腾出来的空间是无法被新插入的数据使用的,表只会越来越大。并且无法按照普通表的方式去reorg表,会报错SQL1667N:

db2 reorg table t0417
SQL1667N  The operation failed because the operation is not supported with the
type of the specified table.  Specified table: "T0417".  Table type: "ORGANIZE
BY COLUMN".  Operation: "REORG TABLE".  SQLSTATE=42858

信息中心的页面 Space reclamation for column-organized tables 中有明确说明,列式存储的表删除数据以后的空间不能被复用

When a row in a column-organized table is deleted, the row is logically (but not physically) deleted. As a result, the space that is used by the deleted row is not available to subsequent transactions, and remains unusable until space reclamation occurs. For example, consider the case where a table is created and 1 million rows are inserted in batch operation A. The size of the table on disk after batch operation A is 5 MB. After some time, batch operation B inserts another 1 million rows. Now the table consumes 10 MB on disk. At this point, all of the rows that were inserted in batch operation A are deleted, and the table size on disk remains 10 MB. If a third batch operation C inserts another 1 million rows into the table, 5 MB of additional space is required. (With row-organized tables, the rows that are inserted in batch operation C would use the space that was vacated by the deleted rows from batch operation A.) A REORG TABLE command is required to reclaim the space that was used by the rows inserted in batch operation A.

因此,删除完数据之后,要想回收空间,必须做reorg操作,而且要带有RECLAIM EXTENTS参数:

db2 reorg table tablename RECLAIM EXTENTS ALLOW WRITE ACCESS

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0061069.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值