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

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

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

Last updated: November 25, 2020 - 5:56 pm UTC

Version: 12.1.0.2.0

Viewed 100+ 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 we 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.

More to Explore

Administration

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