Greetings,

I have to delete older policies and its related records in other
tables.

The deletion from the parent table will trigger the deletion of
relevant records from about 30 something tables that are defined with
DELETE CASCADE.

The delete SQL goes like this:

delete from policy  p1
where       (p1.tranid,  p1.extract_dt)
 in ( select  p2.tranid, min(p2.extract_dt)
       from policy p2
       group by  p2.tranid);

The size for policy table is about 150K rows and there may be 1:1 or
1:few records ratio between parent and child tables.

All foreign keys are indexed and clustred.

The SQL did not return result for a few hours.

Any suggestions to speed up the query?

Many Thanks!