DELETE FROM PPI - VERY SLOW

Database

DELETE FROM PPI - VERY SLOW

Hi,

I have a table with a PPI which I am trying to delete from. However the explain states it would take 43 seconds. I would have expected it to be a near instant transaction as it’s a single partition operation.

The PPI is:

PARTITION BY RANGE_N(WEEK_END_DATE BETWEEN DATE '2009-12-06' AND DATE '2020-12-31' EACH INTERVAL '7' DAY )

And the code is:

DELETE FROM TABLE1
WHERE WEEK_END_DATE = 1091206
;

All stats have been collected (including PARTITION).

Any ideas on what could cause this or are partitioned deletes inherently slow?
5 REPLIES

Re: DELETE FROM PPI - VERY SLOW

Do you also have NUSIs and join indexes defined on the table? These will also have to be updated as well.

Random?
N/A

Re: DELETE FROM PPI - VERY SLOW

The delete will be fast if it's deleting a full partition (similar to a fast-path delete without where-condition).
But in your case it's just a one day out of 7 so it must Transient Journal those rows.

Did you actually run it to see if it's just a wrong guess of the optimizer?

Compare the exlain to a full partition delete:
DELETE FROM TABLE1
WHERE WEEK_END_DATE between 1091206 and 1091206 + 6

Dieter

Re: DELETE FROM PPI - VERY SLOW

Hello Dieter,

If we do a full partition delete, does transient journalling happen?

If we run a delete that deletes a subset of a partition or a subset of partitions(eg: table has partition on date column and we are delete from a table where date_column=<value> and some_other_column=<value>), does transient journalling happen?

Regards,

Suhail

N/A

Re: DELETE FROM PPI - VERY SLOW

Hi Suhail,

#1: full partition DELETE should ru without TJ unless there are Foreign Keys, Triggers, Join/Hash Indexes.

#2: Of course there will be TJ because it's not deleting all rows from that partition.

Dieter

Re: DELETE FROM PPI - VERY SLOW

Thank you Dieter.

-Suhail