oracle 我们可以有一个 where 子句用于从 <tab> 分区 <part.> 中删除吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5324709/
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
Can we have a where clause for delete from <tab> partition <part.>?
提问by Balualways
I have a table partitioned based on time stamp (like partition1will have 9 months old data, partition2have 6 months old data, partition3have 3 months old data and so on)
我有一个基于时间戳分区的表（比如partition1有 9 个月的数据，partition2有 6 个月的数据，partition3有 3 个月的数据等等）
I need to delete data based on a condition on a specific partition.
我需要根据特定分区上的条件删除数据。
delete from table1 partition partition3
where group id in ( select * from table1 where group_code='pilot');
Is this operation will delete only from partiton3?
此操作是否只会删除 from partiton3？
回答by Ronnis
First of all, the syntax is:
首先，语法是：
delete from table1 partition(partition3)
Secondly, you shouldn'trefer to partition names. It creates a dependency on the physical layout of the table. It may be partitioned monthly today, but it may be repartitioned on weeks or days sometime in the future. When this happens, your code will break. Oracle can infer the partition just fine from a predicate on the partitioning column. Try to forget about the table being partitioned.
其次，您不应该引用分区名称。它创建了对表物理布局的依赖。今天可能会按月分区，但可能会在未来的某个时间在几周或几天内重新分区。发生这种情况时，您的代码将中断。Oracle 可以根据分区列上的谓词很好地推断出分区。尽量忘记正在分区的表。
Third, your subselect will fail because of select *being compared to ID.
第三，您的子选择将因select *与 ID 进行比较而失败。
The statement you are looking for likely looks something like this:
您正在寻找的语句可能如下所示：
delete
from table1
where group_code = 'pilot'
and partition_column = 'some value';
回答by Rob van Wijk
Partitioning works transparently, meaning that you can simply do a regular
分区透明地工作，这意味着您可以简单地做一个常规的
delete table1
where group_id in (select group_id from table2 where group_code = 'pilot')
and your_date_column
between trunc(sysdate,'mm') - interval '3' month
and trunc(sysdate,'mm') - interval '2' month
The optimizer will know that the second predicate means that only data from partition3 needs deleting.
优化器会知道第二个谓词意味着只有来自 partition3 的数据需要删除。
Regards,
Rob.
问候，
罗伯。
