在Oracle中重新排序表列

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:

如果我错了请纠正我，但我认为一般步骤是:

1. Rename the existing table.
2. 重命名现有表。
3. Drop the primary key constraint.
4. 删除主键约束。
5. Re-create the table with the correct column order.
6. 使用正确的列顺序重新创建表。
7. List item
8. 列表项
9. Run INSERT INTO .. SELECT to move the data from temp to the table in step #3.
10. 运行插入……选择将数据从temp移动到步骤#3中的表。
11. Drop the temp table.
12. 删除临时表。

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.

编辑:不真诚的感谢那些质疑为什么它需要做而不是提供帮助的人。为了回答你的问题，为什么需要这样做:我听从了别人的命令，他们说我需要这样做，列的顺序也很重要。我对此的看法是无关紧要的。

4 个解决方案

#1

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.
• 可更新varchar列。
• Indexed columns after other searchable columns.
• 其他可搜索列之后的索引列。

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.

这些规则相互冲突，并没有在最新版本中对其性能进行测试。大多数都经过了实践测试，但我没有记录结果。放置选项的目标是三个相互冲突的目标之一:容易理解的列放置;快速数据检索;以及对更新的最小数据移动。

#2

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;

描述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新特性的一篇文章中了解到这一点。

#3

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:

这里有一个稍微危险的方法，有点快又脏:

1. Ensure you have enough space to copy the Table
2. 确保您有足够的空间来复制该表
3. Note any Constraints, Grants, Indexes, Synonyms, Triggers, um.. maybe some other stuff - that belongs to a Table - that I haven't thought about?
4. 注意任何约束、授权、索引、同义词、触发器。也许还有别的东西——属于一张桌子的东西——我没想过?
5. CREATE TABLE table_right_columns AS SELECT column1 column3, column2 FROM table_wrong_columns; -- Notice how we correct the position of the columns :)
6. 创建表table_right_columns，如SELECT column1 column3, column2 FROM table_wrong_columns;——注意我们如何纠正列的位置:)
7. DROP TABLE table_wrong_columns;
8. 删除表table_wrong_columns;
9. 'ALTER TABLE table_right_columns RENAME TO table_wrong_columns;`
10. 'ALTER TABLE table_right_columns重命名为table__columns; '
11. Now the yucky part: recreate all those items you noted in step 2 above
12. 现在是恶心的部分:重新创建上面步骤2中提到的所有项目
13. Check what code is now invalid, and recompile to check for errors
14. 检查什么代码现在无效，并重新编译以检查错误。

And next time you create a table, please consider the future requirements! ;)

下次创建表时，请考虑将来的需求!,)

#4

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，因为从UNORDERDED_POSITION中选择co1_1、col_3、col_2应该会有所帮助。

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

由于迪利普