oracle Oracle如何从表中删除少数分区数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26728011/
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
Oracle how to delete from a table except few partitions data
提问by Bolimera Hannah
I have a big table with lot of data partitioned into multiple partitions. I want to keep a few partitions as they are but delete the rest of the data from the table. I tried searching for a similar question and couldn't find it in stackoverflow. What is the best way to write a query in Oracle to achieve the same?
我有一个大表，里面有很多数据分成多个分区。我想保留一些分区，但删除表中的其余数据。我尝试搜索类似的问题，但在 stackoverflow 中找不到。在 Oracle 中编写查询以实现相同的最佳方法是什么？
回答by APC
It is easy to delete data from a specific partition: this statement clears down all the data for February 2012:
从特定分区中删除数据很容易：此语句清除了 2012 年 2 月的所有数据：
delete from t23 partition (feb2012);
A quicker method is to truncate the partition:
一个更快的方法是截断分区：
alter table t23 truncate partition feb2012;
There are two potential snags here:
这里有两个潜在的障碍：
- Oracle won't let us truncate partitions if we have foreign keys referencing the table.
- The operation invalidates any partitioned Indexes so we need to rebuild them afterwards.
- 如果我们有引用表的外键，Oracle 不会让我们截断分区。
- 该操作使任何分区索引无效，因此我们需要在之后重建它们。
Also, it's DDL, so no rollback.
此外，它是 DDL，所以没有回滚。
If we never again want to store data for that month we can drop the partition:
如果我们再也不想存储该月的数据，我们可以删除分区：
alter table t23 drop partition feb2012;
The problem arises when we want to zap multiple partitions and we don't fancy all that typing. We cannot parameterise the partition name, because it's an object name not a variable (no quotes). So leave only dynamic SQL.
当我们想要切换多个分区并且我们不喜欢所有类型的输入时，问题就会出现。我们不能参数化分区名称，因为它是一个对象名称而不是一个变量（没有引号）。所以只留下动态 SQL。
As you want to remove most of the data but retain the partition structure truncating the partitions is the best option. Remember to invalidate any integrity constraints (and to reinstate them afterwards).
由于您想删除大部分数据但保留分区结构，因此截断分区是最佳选择。请记住使任何完整性约束无效（并在之后恢复它们）。
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;
/
You should definitely run the loop first with execute immediatecall commented out, so you can see which partitions your WHERE clause is selecting. Obviously you have a back-up and can recover data you didn't mean to remove. But the quickest way to undertake a restore is not to need one.
您绝对应该首先运行循环并execute immediate注释掉 call，这样您就可以看到您的 WHERE 子句正在选择哪些分区。显然，您有备份并且可以恢复您不想删除的数据。但是进行恢复的最快方法是不需要恢复。
Afterwards run this query to see which partitions you should rebuild:
然后运行此查询以查看您应该重建哪些分区：
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';
You can automate the rebuild statements in a similar fashion.
您可以以类似的方式自动执行重建语句。
" I am thinking of copying the data of partitions I need into a temp table and truncate the original table and copy back the data from temp table to original table. "
“我正在考虑将我需要的分区的数据复制到临时表中并截断原始表并将数据从临时表复制回原始表。”
That's another way of doing things. With exchange partition it might be quite quick. It might also be slower. It also depends on things like foreign keys and indexes, and the ratio of zapped partitions to retained ones. If performance is important and/or you need to undertake this operation regularly then you should to benchmark the various options and see what works best for you.
那是另一种做事的方式。使用交换分区可能会很快。它也可能更慢。它还取决于诸如外键和索引之类的东西，以及被删除的分区与保留分区的比率。如果性能很重要和/或您需要定期进行此操作，那么您应该对各种选项进行基准测试，看看哪种最适合您。
