Oracle如何从表中删除除少数分区以外的数据

Question

我有一个大表，其中有许多数据被分区到多个分区中。我希望保留一些分区的原样，但删除表中的其余数据。我试着搜索一个类似的问题，但在stackoverflow中找不到。在Oracle中编写查询以实现相同目的的最佳方法是什么？

Answer 1

从特定分区中删除数据很容易:此语句清除2012年2月的所有数据：

delete from t23 partition (feb2012);

一种更快的方法是截断分区：

alter table t23 truncate partition feb2012;

这里有两个潜在的障碍：

如果有引用该表的外键，

1. Oracle将不允许我们截断分区。
2. 该操作将使任何分区索引无效，因此我们需要在以后重新构建它们。

另外，它是DDL，所以没有回滚。

如果我们再也不想存储该月的数据，我们可以删除该分区：

alter table t23 drop partition feb2012;

当我们想要清除多个分区，并且我们不喜欢所有的类型时，问题就出现了。我们不能参数化分区名，因为它是一个对象名而不是一个变量(没有引号)。所以只留下动态SQL。

由于您希望删除大部分数据，但保留分区结构，因此截断分区是最好的选择。记住要使任何完整性约束无效(并在之后恢复它们)。

declare
    stmt varchar2(32767);
begin
    for lrec in ( select partition_name
                  from user_tab_partitions
                  where table_name = 'T23'
                  and partition_name like '%2012'
                )
    loop
        stmt := 'alter table t23 truncate partition '
                    || lrec.partition_name
                  ;
        dbms_output.put_line(stmt);
        execute immediate stmt;
    end loop;
end;
/

您绝对应该首先运行循环，并将execute immediate调用注释掉，这样您就可以看到WHERE子句选择了哪些分区。显然，你有一个备份，可以恢复你不想删除的数据。但是，进行恢复的最快方法是不需要恢复。

然后运行以下查询，查看应该重建哪些分区：

select ip.index_name, ip.partition_name, ip.status 
from user_indexes i
     join user_ind_partitions ip
      on  ip.index_name = i.index_name
where i.table_name = 'T23'
and ip.status = 'UNUSABLE';

您可以以类似的方式自动执行rebuild语句。

“我正在考虑将所需分区的数据复制到临时表中，并截断原始表，然后将数据从临时表复制回原始表。”

这是另一种做事的方式。使用exchange分区可能会非常快。它也可能更慢。它还依赖于外键和索引，以及已分区分区与保留分区的比率。如果性能很重要，并且/或者您需要定期执行此操作，那么您应该对各种选项进行基准测试，并查看哪些选项最适合您。

Answer 2

从分区表中删除分区时必须非常小心。分区表通常用于大数据表，如果(且仅当)您在表上有全局索引，drop Partition会使您的全局索引无效，您应该在大表中重建全局索引，这是灾难。

为了最大限度地减少此场景中对表的查询的副作用，我首先删除分区中的记录并将其设为空分区，然后使用

ALTER TABLE table_name DROP PARTITION partition_name UPDATE GLOBAL INDEXES;

删除空分区而不使我的全局索引无效。