在Oracle中重新排序表的列 [英] Re-order columns of table in Oracle

问题描述

我有一个包含50多个列的表，我需要交换前两列的顺序.使用Oracle完成此任务的最佳方法是什么?假设表名称为ORDERDETAILS，并且前两列为ITEM_ID和ORDER_ID.重命名完成后，表名仍应为ORDERDETAILS，但前两列将为ORDER_ID和ITEM_ID. FWIW，列类型以及其余列及其顺序为irelevent.

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.

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

Correct me if I'm wrong, but I think the general steps are:

1. 重命名现有表.
2. 删除主键约束.
3. 以正确的列顺序重新创建表.
4. 列表项
5. 运行INSERT INTO .. SELECT，在步骤#3中将数据从临时表移到表中.
6. 删除临时表.

我在Oracle方面的经验很少，所以也许我错过了一两个步骤.

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?

SQL示例倍受赞赏.

SQL examples are much appreciated.

非常感谢那些质疑为什么需要这样做而不是提供帮助的人.要回答您为什么需要这样做的问题:我正在跟别人说我需要这样做，而这些列的顺序确实很重要.我对此的想法/看法是无聊的.

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.

推荐答案

查看包DBMS_Redefinition.它将使用新的顺序重建表.可以在线上完成表格.

Look at the package DBMS_Redefinition. It will rebuild the table with the new ordering. It can be done with the table online.

正如菲尔·布朗(Phil Brown)所述，在进行此操作之前请仔细考虑.但是，扫描行中的列并在更新时移动数据会产生开销.我使用的列排序规则(无特定顺序):

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):

• 将相关列分组在一起.
• 可为空的列之前为非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.

这篇关于在Oracle中重新排序表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！