0

I want to delete around 1 million records from a table which is partitioned and table size is around 10-13 millions , As of now only 2 partition exist in the table containining July month data and august month data, and i want to delete from July month.Can you please let me know if a simple delete from table paritition (0715) is ok to do ? Possibilities of fragmentation ? or any best way out?

Thank you

aadi
  • 27
  • 1
  • 5
  • table structure - I have an accountnumber column , month column and 12 other columns .. partition is on month column. based on account number i will be deleting from the partition – aadi Oct 13 '15 at 06:54

2 Answers2

3

DELETE is rather costly operation on large partitioned tables (but 10M is not realy large). Typically you try to avoid it and remove the data partition-wise using drop partition.

The simplest schema is rolling window, where you define a range partitioning schema by dropping the oldest partitian after the retention interval.

If you need more controll you may use CTAS and exchange back approach. Instead of deleting a large part of a partition create a copy of it 

 create table TMP as
 select * from TAB  PARTITION  (ppp)
 where <predicate to filter out records to be ommited for partition ppp>

Create indexes on the TMP table in the same structure as the LOCAL indexes of the partitioned table. Than exchange the temporary table with the partition 

ALTER TABLE TAB 
EXCHANGE PARTITION ppp WITH TABLE TMP including indexes
WITHOUT VALIDATION

Note no fragmenatation as a result, in contrary you may use it to reorganize the partition data (e.g. with ORDER BY in CTAS or with COMPRESS etc.)

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • Just a small note. This approach can require some tweaking in OTLP systems where you have various FK/UK constraints. The table to be exchanged might need to have the same FK constraints on it. Also global indexes might need to be rebuilt. – ibre5041 Oct 13 '15 at 12:18
0

You can delete truncate the partition from the given table. Delete also you can perform if you want to delete few rows from the partition. Plz share your table structure along with the partition details so that it will be easy for people here to assist you.

Pravin Satav
  • 702
  • 5
  • 17
  • 36