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.