oracle 在负载下添加具有默认值的列的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10633960/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to add column with default value while under load
提问by Ben George
When adding a column to a table that has a default value and a constraint of not null. Is it better to run as a single statement or to break it into steps while the database is under load.
将列添加到具有默认值和非空约束的表时。作为单个语句运行还是在数据库处于负载状态时将其分解为多个步骤更好。
ALTER TABLE user ADD country VARCHAR2(4) DEFAULT 'GB' NOT NULL
VERSUS
相对
ALTER TABLE user ADD country VARCHAR2(2)
UPDATE user SET country = 'GB'
COMMIT
ALTER TABLE user MODIFY country DEFAULT 'GB' NOT NULL
采纳答案by bitmagier
Performance depends on the Oracle version you use. Locks are generated anyway.
性能取决于您使用的 Oracle 版本。无论如何都会生成锁。
If version <= Oracle 11.1 then #1 does the same as #2. It is slow anyway. Beginning with Oracle 11.2, Oracle introduced a great optimization for the first statement (one command doing it all). You don't need to change the command - Oracle just behaves differently. It stores the default value only in data dictionary instead of updating each physical row.
如果版本 <= Oracle 11.1，则 #1 与 #2 的作用相同。反正很慢。从 Oracle 11.2 开始，Oracle 为第一条语句（一个命令完成所有操作）引入了一个很好的优化。您不需要更改命令 - Oracle 只是行为不同。它仅将默认值存储在数据字典中，而不更新每个物理行。
But I also have to say, that I encountered some bugs in the past related to this feature (in Oracle 11.2.0.1)
但我也不得不说，我过去遇到了一些与此功能相关的错误（在 Oracle 11.2.0.1 中）
- failure of traditional import if export was done with direct=Y
- merge statement can throw an ORA-600 [13013] (internal oracle error)
- a performance problem in queries using such tables
- 如果使用 direct=Y 完成导出，则传统导入失败
- merge 语句会抛出 ORA-600 [13013]（oracle 内部错误）
- 使用此类表的查询中的性能问题
I think this issues are fixed in current version 11.2.0.3, so I can recommend to use this feature.
我认为这个问题在当前版本 11.2.0.3 中得到了修复，所以我可以推荐使用这个功能。
回答by Dewfy
Some time ago we have evaluated possible solutions of the same problem. On our project we had to remove all indexes on table, perform altering and restore indexes back.
前段时间我们评估了同一问题的可能解决方案。在我们的项目中，我们必须删除表上的所有索引，执行更改并恢复索引。
回答by David Aldridge
If your system needs to be using the table then DBMS_Redefinition is really your only choice.
如果您的系统需要使用该表，那么 DBMS_Redefinition 确实是您唯一的选择。
