Oracle - 向表添加列需要永远
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10561279/
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
Oracle - Adding columns to a table takes forever
提问by Mikayil Abdullayev
I need to add two DATE columns to an already existing table. But it takes very long and I have to kill the process. What is strange is I was able to add those columns to the other tables in the same database momentarily. Most of those tables are larger than the one I'm having trouble with both data and column-count-wise. What do I have to do to be able to add those new columns?
我需要将两个 DATE 列添加到一个已经存在的表中。但这需要很长时间，我必须终止该过程。奇怪的是，我能够立即将这些列添加到同一数据库中的其他表中。这些表中的大多数都比我在数据和列数方面都遇到问题的表大。我必须做什么才能添加这些新列？
回答by Mikayil Abdullayev
here's how I solved the problem. Before, I was specifying a default value for the columns right at the moment of adding them. But then I first added the columns with no default values. After columns got added I specified the default value and it executed immediately with no more waiting. Thank you very much @Justin Cave for your hint about Default value. That was the key point.
这是我解决问题的方法。之前，我在添加列的那一刻就为列指定了默认值。但后来我首先添加了没有默认值的列。添加列后，我指定了默认值，它立即执行，不再等待。非常感谢@Justin Cave 提供有关默认值的提示。这是关键点。
I have no doubt that it's related with the fact that when specifying default value at the time of adding column that default value is written to all the records inserted earlier.So if there are 5 million records in the table, that table will be updated to set default value for the newly added column for all the rows. Updating 5 million records is expensive as one might guess. But if default value is set after adding a column then the value of that new column in the rows inserted earlier will be NULL, so no update will take place.
我毫不怀疑这与在添加列时指定默认值时将默认值写入之前插入的所有记录的事实有关。因此，如果表中有 500 万条记录，则该表将更新为为所有行的新添加列设置默认值。人们可能会猜到，更新 500 万条记录的成本很高。但是如果在添加一列后设置了默认值，那么之前插入的行中该新列的值将为 NULL，因此不会发生更新。
回答by Wouter
I was also facing this problem when adding a varchar2(4000) column with no default valueone a 61K row table in Oracle 11g. After 30 minutes the column was still not added.
在 Oracle 11g 中向 61K 行表添加没有默认值的 varchar2(4000) 列时，我也遇到了这个问题。30 分钟后仍未加入柱子。
I tried to abort the operation, and retried it, but it still didn't finish.
我试图中止操作，然后重试，但仍然没有完成。
What worked:
I tried adding a number column instead, that took a split second. Dropped it.
Then i tried adding a varchar2(1) column, that took a split second. Dropped it.
Then i tried adding a varchar2(4000) column again, and it only took a split second.
什么有效：
我尝试添加一个数字列，这花了一秒钟。丢了。
然后我尝试添加一个 varchar2(1) 列，这花了一秒钟。丢了。
然后我再次尝试添加一个 varchar2(4000) 列，只花了一秒钟。
Silly "workaround", but maybe this also works for anyone else facing this problem.
愚蠢的“解决方法”，但也许这也适用于面临此问题的任何其他人。
