Skip to Main Content
  • Questions
  • Delete on referenced partitioned table taking time

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: November 24, 2020 - 2:12 pm UTC

Last updated: November 14, 2022 - 1:32 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Connor, Chris,

I have referenced partitioned tables as below
Its a daily range partition on ORDER_DATE column.

CREATE TABLE ORDERS
(
  ORDER_ID                    NUMBER(11),
  PART_DATE              DATE          DEFAULT trunc(sysdate),
  ....
  150 columns
  ....
)
PARTITION by range (PART_DATE) (
    partition ORDERS_P_01_JAN_2018 values less than (to_date('02-JAN-2018','DD-MON-YYYY')),
    partition ORDERS_P_02_JAN_2018 values less than (to_date('03-JAN-2018','DD-MON-YYYY')),
    partition ORDERS_P_03_JAN_2018 values less than (to_date('04-JAN-2018','DD-MON-YYYY')),
    partition ORDERS_P_04_JAN_2018 values less than (to_date('05-JAN-2018','DD-MON-YYYY')),
    partition ORDERS_P_MAXVALUE  values less than (maxvalue)
);

CREATE TABLE ORDERS_DETAILS
(
  ORDER_ID               NUMBER(11)         NOT NULL,
  ....
  70 columns
  ....
  CONSTRAINT FK_ORDER_ID 
  FOREIGN KEY (ORDER_ID) 
  REFERENCES ORDERS (ORDER_ID)

)
PARTITION BY REFERENCE (FK_ORDER_ID);



The issue here is - it takes significant time to delete records from both child "ORDER_DETAILS" as well as parent "ORDERS".
Daily partition record count of each table is around 800k in "ORDERS" & 2.7 million in "ORDER_DETAILS" and these table holds 2 months of data and rest gets purged on daily basis (using native delete statement).

Earlier we were deleting these records using 50k records per iteration followed by commit.
Then we tried the limit counter with 5k but it was taking same to finish purging of all records for that day.

The next option we are planning to truncate partition instead of delete statement.
But before doing so, would like to seek your help so that i can check more on delete taking time.

Could you please suggest something which can be checked at our end.

and Chris said...

Deleting millions of rows will take "a while"

Truncating a table (partition) is "instant"

It looks like you've got daily partitions and want to delete the data daily. So the question is:

Why are you using delete instead of truncate partition?

One of the big benefits of partitioning is it makes removing old data fast via drop/truncate partition.

Just use truncate - you need a very strong reason to continue trying to optimize the delete.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 13, 2022 - 5:15 pm UTC

I tried using DROP partition instead of delete statement. I am able to drop partition for child table without any issue. But when I tried dropping partition for the same date from parent table,it gave me error that unique/primary key referenced by foreign key. What can I do to overcome this?
Chris Saxon
November 14, 2022 - 1:32 pm UTC

You need to remove child rows affected by the drop. I'm guessing that there's a mismatch between which partitions the parent and child rows are in.

This is one of the reasons reference partitioning was created https://docs.oracle.com/en/database/oracle/oracle-database/21/vldbg/partition-concepts.html#GUID-54D18B18-6838-4115-9389-E1FB0D20A8CA

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database