Alter by Table Rename 与 Alter Table in Place

今天技术群里的某位兄弟遇到了一个奇怪的错误：

Error: Cannot alter record EP_APPR_B_ITEM using Alter by Table Rename because one or more deleted field(s) has LOB storage parameters. Please alter using Alter in Place. 
Error: EP_APPR_B_ITEM - Alter 进程不成功。 (76,42)

 经查，原来是Alter表的时候使用了Alter by Table Rename选项：

其实只需要选择Alter in Place 就可以解决上面的问题了。下面是PeopleBook的解释：

Alter Table Options

When altering tables, the alter process takes care of the indexes regardless of the Alter Table Options you select on the Alter tab of the Build Settings dialog box. In the case of Alter by Table Rename, the indexes are dropped automatically when the temp table is dropped. For the Alter in Place option, the index creation process goes through the Recreated index only if modified option on the Create tab.

• Alter in Place

  Selected by default. For database platforms in which Alter in Place is not supported, alter is automatically done by Alter by Table Rename (even if that option is not selected).

  Database platforms vary in the capabilities for Alter in Place table operations. The following table displays the limitations for each operation by platform:

  Database Platforms	Add Column	Change Column (data type, size, precision, scale, and null/not null property change)	Rename Table	Delete Column
  Microsoft SQL Server	Yes *	Yes *	Yes *	Yes *
  Oracle	Yes *	No	Yes	Yes *
  DB2/UDB for OS/390 and z/OS	Yes *	Yes *	Yes *	No
  DB2/UDB for Linux, UNIX, and Windows	Yes *	No	No	No
  Sybase	No	No	Yes	No
  Informix	Yes	Yes	Yes	Yes

  * Certain restrictions might apply to this operation. Refer to the documentation delivered with your specific database platform for more information.

• Alter by Table Rename

  Select to create a temporary table (with changes made to the original table or its fields); the data from the original table is imported into the temporary table. The original table is then dropped and the temporary table is renamed to the original table.

  If a table is renamed from the old name to a new name, the indexes that were created on the old table are moved to the new table, but the index names remain in the old table’s name. With Alter by Table Rename selected, the indexes of the old table are dropped before being renamed to the new table name and the indexes are re-created after the table is renamed to the new name. This way, the index is created in the new table’s name.

对于Alter in place ，索引创建进程会执行Create tab上的 Recreated index only if modified 选项：

如果key structure没有改变，Alter in place只会在适当的时候recreate index。

当表包含大量的数据且key structure没有改变时，Ater in place就是一个很好的选择。