SQL 在 Oracle 中重新排序表的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4939735/
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
Re-order columns of table in Oracle
提问by Ryan Rodemoyer
I have a table with 50+ columns and I need to swap the order of the first two columns. What is the best way to accomplish this using Oracle? Assume the table name is ORDERDETAILS and as it is, the first two columns are ITEM_ID and ORDER_ID. Once the rename is complete, the table name should still be ORDERDETAILS but the first two columns will be ORDER_ID and ITEM_ID. FWIW, column types and the rest of the columns and their order is irelevent.
我有一个包含 50 多列的表，我需要交换前两列的顺序。使用 Oracle 完成此任务的最佳方法是什么？假设表名是 ORDERDETAILS，而前两列是 ITEM_ID 和 ORDER_ID。重命名完成后，表名仍应为 ORDERDETAILS，但前两列将是 ORDER_ID 和 ITEM_ID。FWIW，列类型和其余列及其顺序无关紧要。
Correct me if I'm wrong, but I think the general steps are:
如果我错了，请纠正我，但我认为一般步骤是：
- Rename the existing table.
- Drop the primary key constraint.
- Re-create the table with the correct column order.
- List item
- Run INSERT INTO .. SELECT to move the data from temp to the table in step #3.
- Drop the temp table.
- 重命名现有表。
- 删除主键约束。
- 使用正确的列顺序重新创建表。
- 项目清单
- 运行 INSERT INTO .. SELECT 将数据从 temp 移动到第 3 步中的表中。
- 删除临时表。
I have little experience with Oracle so perhaps I'm missing a step or two.
我对 Oracle 的经验很少，所以也许我错过了一两步。
Does a primary key imply an index in Oracle? Does dropping the primary key also drop the index?
主键是否意味着 Oracle 中的索引？删除主键也会删除索引吗？
SQL examples are much appreciated.
非常感谢 SQL 示例。
EDIT: Un-sincere thanks to those who question why it needs done instead of providing help. To answer your question as to why it needs done: I'm following orders from someone else who says I need to do it this way and the order of the columns DOES matter. My thoughts/opinions on this are irrelevent.
编辑：不真诚地感谢那些质疑为什么需要完成而不是提供帮助的人。回答您关于为什么需要这样做的问题：我正在听从其他人的命令，他说我需要这样做，并且列的顺序很重要。我对此的想法/意见是无关紧要的。
采纳答案by BillThor
Look at the package DBMS_Redefinition. It will rebuild the table with the new ordering. It can be done with the table online.
查看包 DBMS_Redefinition。它将使用新的顺序重建表。它可以通过在线表格完成。
As Phil Brown noted, think carefully before doing this. However there is overhead in scanning the row for columns and moving data on update. Column ordering rules I use (in no particular order):
正如 Phil Brown 所指出的，在执行此操作之前请仔细考虑。但是，在扫描行以获取列并在更新时移动数据时会产生开销。我使用的列排序规则（无特定顺序）：
- Group related columns together.
- Not NULL columns before null-able columns.
- Frequently searched un-indexed columns first.
- Rarely filled null-able columns last.
- Static columns first.
- Updateable varchar columns later.
- Indexed columns after other searchable columns.
- 将相关的列组合在一起。
- 在可以为空的列之前不是 NULL 列。
- 首先经常搜索未索引的列。
- 很少最后填充可为空的列。
- 首先是静态列。
- 稍后可更新 varchar 列。
- 其他可搜索列之后的索引列。
These rules conflict and have not all been tested for performance on the latest release. Most have been tested in practice, but I didn't document the results. Placement options target one of three conflicting goals: easy to understand column placement; fast data retrieval; and minimal data movement on updates.
这些规则相互冲突，并且尚未在最新版本上进行性能测试。大多数已经在实践中进行了测试，但我没有记录结果。放置选项针对三个相互冲突的目标之一：易于理解的列放置；快速数据检索；和最小的更新数据移动。
回答by Jonas Meller
Since the release of Oracle 12c it is now easier to rearrange columns logically.
自 Oracle 12c 发布以来，现在可以更轻松地按逻辑重新排列列。
Oracle 12c added support for making columns invisible and that feature can be used to rearrange columns logically.
Oracle 12c 添加了对使列不可见的支持，并且该特性可用于在逻辑上重新排列列。
Quote from the documentation on invisible columns:
When you make an invisible column visible, the column is included in the table's column order as the last column.
当您使不可见列可见时，该列将作为最后一列包含在表的列顺序中。
Example
例子
Create a table:
创建一个表：
CREATE TABLE t (
a INT,
b INT,
d INT,
e INT
);
Add a column:
添加一列：
ALTER TABLE t ADD (c INT);
Move the column to the middle:
将列移到中间：
ALTER TABLE t MODIFY (d INVISIBLE, e INVISIBLE);
ALTER TABLE t MODIFY (d VISIBLE, e VISIBLE);
DESCRIBE t;
DESCRIBE t;
Name
----
A
B
C
D
E
Credits
学分
I learned about this from an article by Tom Kyte on new features in Oracle 12c.
我从Tom Kyte 撰写的一篇关于 Oracle 12c 新特性的文章中了解到这一点。
回答by grokster
It's sad that Oracle doesn't allow this, I get asked to do this by developers all the time..
遗憾的是 Oracle 不允许这样做，开发人员一直要求我这样做。
Here's a slightly dangerous, somewhat quick and dirty method:
这是一个有点危险，有点快速和肮脏的方法：
- Ensure you have enough space to copy the Table
- Note any Constraints, Grants, Indexes, Synonyms, Triggers, um.. maybe some other stuff - that belongs to a Table - that I haven't thought about?
CREATE TABLE table_right_columns AS SELECT column1 column3, column2 FROM table_wrong_columns; -- Notice how we correct the position of the columns :)DROP TABLE table_wrong_columns;- 'ALTER TABLE table_right_columns RENAME TO table_wrong_columns;`
- Now the yucky part: recreate all those items you noted in step 2 above
- Check what code is now invalid, and recompile to check for errors
- 确保您有足够的空间来复制表格
- 请注意任何约束、授权、索引、同义词、触发器，嗯……也许是其他一些我没有考虑过的东西——属于表的东西？
CREATE TABLE table_right_columns AS SELECT column1 column3, column2 FROM table_wrong_columns; -- Notice how we correct the position of the columns :)DROP TABLE table_wrong_columns;- 'ALTER TABLE table_right_columns 重命名为 table_wrong_columns;`
- 现在是令人讨厌的部分：重新创建您在上面第 2 步中记下的所有项目
- 检查哪些代码现在无效，并重新编译以检查错误
And next time you create a table, please consider the future requirements! ;)
下次创建表时，请考虑将来的要求！;)
回答by ajz
I followed the solution above from Jonas and it worked well until I needed to add a second column. What I found is that when making the columns visible again Oracle does not necessarily set them visible in the order listed in the statement.
我遵循了 Jonas 的上述解决方案，它运行良好，直到我需要添加第二列。我发现当使列再次可见时，Oracle 不一定按照语句中列出的顺序将它们设置为可见。
To demonstrate this follow Jonas' example above. As he showed, once the steps are complete the table is in the order that you'd expect. Things then break down when you add another column as shown below:
为了证明这一点，请遵循上面乔纳斯的示例。正如他所展示的，一旦这些步骤完成，表格就会按照您期望的顺序排列。当您添加另一列时，事情就会分解，如下所示：
Example (continued from Jonas'):
示例（接自乔纳斯）：
Add another column which is to be inserted before column C.
添加另一列要插入到 C 列之前。
ALTER TABLE t ADD (b2 INT);
Use the technique demonstrated above to move the newly added B2 column before column C.
使用上面演示的技术将新添加的 B2 列移动到 C 列之前。
ALTER TABLE t MODIFY (c INVISIBLE, d INVISIBLE, e INVISIBLE);
ALTER TABLE t MODIFY (c VISIBLE, d VISIBLE, e VISIBLE);
DESCRIBE t;
DESCRIBE t;
Name
----
A
B
B2
D
E
C
As shown above column C has moved to the end. It seems that the ALTER TABLE statement above processed the columns in the order D, E, C rather than in the order specified in the statement (perhaps in physical table order). To ensure that the column is placed where desired it is necessary to make the columns visible one by one in the desired order.
如上所示，C 列已移至末尾。上面的 ALTER TABLE 语句似乎按照 D、E、C 的顺序而不是语句中指定的顺序（可能是物理表顺序）处理列。为确保将列放置在所需的位置，必须以所需的顺序使列一一可见。
ALTER TABLE t MODIFY (c INVISIBLE, d INVISIBLE, e INVISIBLE);
ALTER TABLE t MODIFY c VISIBLE;
ALTER TABLE t MODIFY d VISIBLE;
ALTER TABLE t MODIFY e VISIBLE;
DESCRIBE t;
DESCRIBE t;
Name
----
A
B
B2
C
D
E
回答by dilip
Use the View for your efforts in altering the position of the column: CREATE VIEW CORRECTED_POSITION AS SELECT co1_1, col_3, col_2 FROM UNORDERDED_POSITION should help.
使用视图来改变列的位置： CREATE VIEW CORRECTED_POSITION AS SELECT co1_1, col_3, col_2 FROM UNORDERDED_POSITION 应该会有所帮助。
This requests are made so some reports get produced where it is using SELECT * FROM [table_name]. Or, some business has a hierarchy approach of placing the information in order for better readability from the back end.
发出此请求是为了在使用 SELECT * FROM [table_name] 的地方生成一些报告。或者，某些业务采用层次结构方法放置信息，以便从后端获得更好的可读性。
Thanks Dilip
谢谢迪利普
