当前位置: 动力学知识库 > 问答 > 编程问答 >

oracle - 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

网友答案:

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

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: