CODEWITHSUNDEEP
oracle-databasedelete-rowpartition
aadi
aadi

Reputation: 27

Delete rows from partition table - Best way

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

Upvotes: 0

Views: 5948

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21063

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.)

Upvotes: 3

Pravin Satav
Pravin Satav

Reputation: 702

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.

Upvotes: 0

Related Questions