Oracle how to delete from a table except few partitions data

Question

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?

Answer 1

It is easy to delete data from a specific partition: this statement clears down all the data for February 2012:

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:

1. Oracle won't let us truncate partitions if we have foreign keys referencing the table.
2. The operation invalidates any partitioned Indexes so we need to rebuild them afterwards.

Also, it's DDL, so no rollback.

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.

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 immediate call 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.

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.

Answer 2

You must very be careful in drop partition from a partition table. Partition table usually used for big data tables and if (and only if) you have a global index on the table, drop partition make your global index invalid and you should rebuild your global index in a big table, this is disaster.

For minimum side effect for queries on the table in this scenario, I first delete records in the partition and make it empty partition, then with

ALTER TABLE table_name DROP PARTITION partition_name UPDATE GLOBAL INDEXES;

drop empty partition without make my global index invalid.