如何从 Oracle 表中删除表分区？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1833618/
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
How can I remove table partitions from an Oracle table?
提问by Kevin Babcock
I've discovered that the partitions used on a particular table are hurting query performance and would like to remove the partitions from the table. Is there an easy way to do this? The table in question has 64 partitions. Based on some initial investigation, I've come up with the following options. Is there a better way?
我发现在特定表上使用的分区会影响查询性能，并希望从表中删除这些分区。是否有捷径可寻？有问题的表有 64 个分区。根据一些初步调查，我提出了以下选项。有没有更好的办法？
- Copy data into another table, drop all partitions, then copy the data back into the original table
- Copy data into another table, drop the original table, then rename the new table and rebuild the indexes
- Use MERGE PARTITION to merge all partitions into a single partition
- 将数据复制到另一个表中，删除所有分区，然后将数据复制回原表
- 将数据复制到另一个表中，删除原表，然后重命名新表并重建索引
- 使用 MERGE PARTITION 将所有分区合并为一个分区
Thoughts?
想法？
回答by APC
Personally I think a variant on option 2 sounds like the best approach.
我个人认为选项 2 的变体听起来是最好的方法。
- Create new table as select * from old table
- Build indexes on new table (*)
- Rename old table to something different
- Rename new table to old table name
- Switch any foreign key constraints from old table to new table. Also transfer any grants, synonyms, etc.
- Drop old table in slow time
- 创建新表作为 select * from old table
- 在新表上建立索引(*)
- 将旧表重命名为不同的名称
- 将新表重命名为旧表名
- 将任何外键约束从旧表切换到新表。还转移任何赠款、同义词等。
- 慢慢放下旧桌子
(*)A non-partitioned table will probably require different indexes from the partitioned one. If you are particularly attached to the index names you can use ALTER INDEX ... RENAME TO ...syntax after step 6.
(*)非分区表可能需要与分区表不同的索引。如果您特别关注索引名称，则可以ALTER INDEX ... RENAME TO ...在步骤 6 之后使用语法。
The advantage of this approach is that it minimises your downtime (basically steps 3, 4 and 5).
这种方法的优点是可以最大限度地减少停机时间（基本上是第 3、4 和 5 步）。
