参考1:
今天技术群里的某位兄弟遇到了一个奇怪的错误:
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的解释:
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
Yes *
Yes *
Yes *
Yes *
Yes *
No
Yes
Yes *
Yes *
Yes *
Yes *
No
DB2/UDB for Linux, UNIX, and Windows
Yes *
No
No
No
No
No
Yes
No
Yes
Yes
Yes
Yes
* Certain restrictions might apply to this operation. Refer to the documentation delivered with your specific database platform for more information.
-
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就是一个很好的选择。
我的记录:
分析:
将Alter by Table Rename改为Alter in Place。原因:Alter by rename首先会创建一个中间表,当表包含很多数据列的时候最好用Alter IN PLANCE。“因为alter by rename会直接将表删除然后重建。如果有大量数据,并且数据库开起了回收站功能,那么这些数据就会被移到另一个逻辑区域即回收站,就会有大量的数据迁移。我的理解是这样。”