oracle 将数据从oracle表中的一列复制到另一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18729983/
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
Copy data from one column to another in oracle table
提问by NebuSoft
My current project for a client requires me to work with Oracle databases (11g). Most of my previous database experience is with MSSQL Server, Access, and MySQL. I've recently run into an issue that seems incredibly strange to me and I was hoping someone could provide some clarity.
我当前的客户项目要求我使用 Oracle 数据库 (11g)。我以前的大部分数据库经验是使用 MSSQL Server、Access 和 MySQL。我最近遇到了一个对我来说似乎非常奇怪的问题，我希望有人能提供一些说明。
I was looking to do a statement like the following:
我希望做如下声明：
update MYTABLE set COLUMN_A = COLUMN_B;
MYTABLE has about 13 million rows.
MYTABLE 大约有 1300 万行。
The source column is indexed (COLUMN_B), but the destination column is not (COLUMN_A)
源列已编入索引 (COLUMN_B)，但目标列未编入索引 (COLUMN_A)
The primary key field is a GUID.
主键字段是一个 GUID。
This seems to run for 4 hours but never seems to complete.
这似乎运行了 4 个小时，但似乎从未完成。
I spoke with a former developer that was more familiar with Oracle than I, and they told me you would normally create a procedure that breaks this down into chunks of data to be commited (roughly 1000 records or so). This procedure would iterate over the 13 million records and commit 1000 records, then commit the next 1000...normally breaking the data up based on the primary key.
我与一位比我更熟悉 Oracle 的前开发人员进行了交谈，他们告诉我您通常会创建一个过程，将其分解为要提交的数据块（大约 1000 条记录）。此过程将迭代 1300 万条记录并提交 1000 条记录，然后提交接下来的 1000 条……通常根据主键分解数据。
This sounds somewhat silly to me coming from my experience with other database systems. I'm not joining another table, or linking to another database. I'm simply copying data from one column to another. I don't consider 13 million records to be large considering there are systems out there in the orders of billions of records. I can't imagine it takes a computer hours and hours (only to fail) at copying a simple column of data in a table that as a whole takes up less than 1 GB of storage.
根据我使用其他数据库系统的经验，这对我来说听起来有些愚蠢。我没有加入另一个表，或链接到另一个数据库。我只是将数据从一列复制到另一列。考虑到有数十亿条记录的系统，我不认为 1300 万条记录很大。我无法想象在一个表中复制一个简单的数据列需要花费数小时（只会失败），而整个表占用的存储空间不到 1 GB。
In experimenting with alternative ways of accomplishing what I want, I tried the following:
在尝试实现我想要的替代方法时，我尝试了以下方法：
create table MYTABLE_2 as (SELECT COLUMN_B, COLUMN_B as COLUMN_A from MYTABLE);
This took less than 2 minutes to accomplish the exact same end result (minus dropping the first table and renaming the new table).
这花了不到 2 分钟的时间来完成完全相同的最终结果（减去删除第一个表和重命名新表）。
Why does the UPDATE run for 4 hours and fail (which simply copies one column into another column), but the create table which copies the entire table takes less than 2 minutes?
为什么 UPDATE 运行了 4 小时并失败（它只是将一列复制到另一列中），而复制整个表的 create table 只用了不到 2 分钟？
And are there any best practices or common approaches used to do this sort of change? Thanks for your help!
是否有任何最佳实践或常用方法可用于进行此类更改？谢谢你的帮助！
回答by SeraM
It does seem strange to me. However, this comes to mind:
这对我来说似乎很奇怪。然而，想到了这一点：
When you are updating the table, transaction logs must be created in case a rollback is needed. Creating a table, that isn't necessary.
更新表时，必须创建事务日志以备需要回滚。创建一个表，这是没有必要的。
