Can we have a where clause for delete from <tab> partition <part.>?

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?

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';

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.