DB2中ALTER TABLE为什么需要REORG操作?
来源:互联网 发布:绿地集团工作知乎 编辑:程序博客网 时间:2024/06/03 16:03
ALTER TABLE操作在日常开发中很常见,下面是摘自DB2官网关于ALTER TABLE操作的一段话。
Perhaps the most important thing to realize when running an ALTER TABLE statement containing a REORG-recommended operation is that once the ALTER TABLE statement has executed, the table will be placed in the Reorg Pending state. This means that the table is inaccessible for almost all operations until you perform a REORG. See the ALTER TABLE statement in the SQL Referencefor the complete list of ALTER TABLE operations, some of which are also called REORG-recommended operations.
简单地说就是运行ALTER TABLE时要注意当前运行的语句是否需要执行REORG操作,对于这样的ALTER TABLE语句,如果不执行REORG操作的话,基本上目标表就不再可用。至于什么样的语句需要REORG,什么样的不需要,看SQL Reference去!下面是一个具体的例子演示:
CREATE TABLE my_test AS ( SELECT id, ... sla_priority1_time, sla_priority2_time, sla_priority3_time, sla_priority4_time, CAST(NULL AS DECIMAL(11, 2)) AS approvedDouAmount, CAST(NULL AS DECIMAL(4)) AS year FROM fin_attributes)WITH NO DATA;ALTER TABLE my_test ALTER COLUMN id SET GENERATED ALWAYS AS IDENTITY;ALTER TABLE my_test ADD COLUMN datetime TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;ALTER TABLE my_test ALTER COLUMN sla_priority1_time DROP NOT NULL;ALTER TABLE my_test ALTER COLUMN sla_priority2_time DROP NOT NULL;ALTER TABLE my_test ALTER COLUMN sla_priority3_time DROP NOT NULL;REORG TABLE my_test;ALTER TABLE my_test ALTER COLUMN sla_priority4_time DROP NOT NULL;REORG TABLE my_test;INSERT INTO my_test ( ... sla_priority1_time, sla_priority2_time, sla_priority3_time, sla_priority4_time, approvedDouAmount, year)SELECT ... sla_priority1_time, sla_priority2_time, sla_priority3_time, sla_priority4_time, NVL(pg.approvedDouAmount, 0), YEAR(NOW()) FROM fin_attributes f, projgrp pg WHERE f.projgrp_id = pg.id AND f.project_id IS NULL AND f.fin_projgrp_id IS NULL UNIONSELECT ... sla_priority1_time, sla_priority2_time, sla_priority3_time, sla_priority4_time, NVL(p.approvedDouAmount, 0), YEAR(NOW()) FROM fin_attributes f, project p WHERE f.project_id = p.id AND f.projgrp_id IS NULL;
代码有四种颜色,绿色代表不需要执行REORG的语句,红色代表需要执行REORG的语句,黄色是REORG语句,白色你懂的。从代码上可以看出,红色高亮语句虽然要求使用REORG,但不及时运行REORG还可以让后续的几个语句继续执行。原因是DB2允许最多三条语句处于Reorg Pending状态,假如去除第一个REORG,语句“ALTER TABLE my_test ALTER COLUMN sla_priority4_time DROP NOT NULL;”就会执行失败。
结论:如果不确定那个是需要REORG哪个是不需要REORG,索性都用上REORG;虽然在允许有三条语句处于Reorg Pending状态,但最好每条ALTER TABLE对应一个REORG,因为处于Reorg Pending状态的表有可能会阻碍后续操作,具体详情请参考文档:http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0023297.htm
PS:REORG TABLE本身是DB2的command,不是正常的SQL语句(Statement)。如果在非命令行环境中想使用REORG的话,可以像下面那样调用存储过程间接执行REORG操作,执行前确保你所使用的帐号有调用这个存储过程的权限:
CALL SYSPROC.ADMIN_CMD('reorg table my_test')
最后来一段用Java动态执行SQL语句或DB2命令的代码。
- DB2中ALTER TABLE为什么需要REORG操作?
- Db2中,为什么ALTER TABLE需要X类型的internal Plan lock?
- DB2 Alter Table导致表处于Reorg Pending状态
- DB2 REORG TABLE
- DB2中修改完表的结构,是否需要REORG操作
- db2中会导致表处于reorg pending状态的alter语句
- DB2 reorg
- db2 reorg
- DB2不允许对表“xx”执行操作,原因码为"7"? 后台执行reorg table +表名
- 在db2数据库中,如何使用命令改变列(alter table, alter column)长度
- mysql alter table 操作
- db2客户端用dbvisualizer无法进行reorg操作
- DB2 Alter Table简介及使用
- DB2 Alter Table简介及使用
- DB2 Alter Table简介及使用
- 会导致表处于reorg pending状态的alter table语句
- DB2 循环 ,导入, Reorg
- 加快alter table 操作速度
- SQLServer数据表分区优化数据库
- CE5.0 驱动程序:设备管理器的源码分析
- Referenced Assembly could not be resolved because it has a dependency conflict 解决方法
- zoj 2157 || poj 1788 Building a New Depot
- 程序员是这样炼成的(9)- 每天有目标的编程
- DB2中ALTER TABLE为什么需要REORG操作?
- css的优先级的几个基本的规则
- 答辩名词解释准备
- grep查找词组
- Trie树原理和实现
- 程序员如何在"小公司成长"和"大公司学习"
- RIM发布全键盘触屏黑莓Bold 9900和OS7
- Oracle笔记 十四、查询XML操作、操作系统文件
- 按钮响应回车事件