我们可以有一个 where 子句来从中删除吗？分区<部分>?

发布于 2024-10-22 12:38:09 字数 332 浏览 3 评论 0原文

我有一个基于时间戳分区的表（例如partition1将有9个月前的数据，partition2有6个月前的数据，partition3有3个月的数据几个月前的数据等等）

我需要根据特定分区上的条件删除数据。

delete from table1 partition partition3
where group id in ( select * from table1 where group_code='pilot');

此操作是否仅从partiton3中删除？

原文

I have a table partitioned based on time stamp (like partition1 will have 9 months old data, partition2 have 6 months old data, partition3 have 3 months old data and so on)

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

七禾 2024-10-29 12:38:09

首先，语法是：

delete from table1 partition(partition3)

其次，您不应该引用分区名称。它创建了对表的物理布局的依赖。今天可能每月进行一次分区，但将来可能会在几周或几天内重新分区。当这种情况发生时，你的代码就会崩溃。 Oracle 可以根据分区列上的谓词很好地推断出分区。尝试忘记正在分区的表。

第三，由于 select * 与 ID 进行比较，您的子选择将失败。

您正在寻找的声明可能如下所示：

delete
  from table1
 where group_code = 'pilot'
   and partition_column = 'some value';

First of all, the syntax is:

delete from table1 partition(partition3)

Secondly, you shouldn't refer 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.

Third, your subselect will fail because of select * being compared to ID.

The statement you are looking for likely looks something like this:

delete
  from table1
 where group_code = 'pilot'
   and partition_column = 'some value';

回复收藏 0 原文

你没皮卡萌 2024-10-29 12:38:09

分区工作透明，这意味着您可以简单地执行常规操作。

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

优化器将知道第二个谓词意味着只有来自分区3的数据需要删除。

问候，
抢。

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.

Regards,
Rob.

回复收藏 0 原文

~没有更多了~