TIps on Delete Cascade performance

[nekiv90]

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!

[Serge Rielau]

ne*****@hotmail.com wrote:

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!

Which platform?
On DB2 V8.1.4 and later let's first get rid of the self join:

delete from
(select row_number() over(partition by tranid order by extract_dt) as rn
from policy) AS D
where rn = 1

Do any of the tables or policy itself have any additional fancyness
(more delete cascade/restrict/no action), after delete triggers)?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[nekiv90]

Hi Serge,

It is on AIX 5.3 DB2 V8.2.2

The Policy table is parent table. The dependant tables could have more
than one RI (ie more than one 'delete cascade') with other tables in
addition to Policy table. Other RI relationship is 'on update no
action'. That will be it.

I'll test out the SQL as suggested.

Thanks again!

Serge Rielau wrote:

ne*****@hotmail.com wrote:

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!

Which platform?
On DB2 V8.1.4 and later let's first get rid of the self join:

delete from
(select row_number() over(partition by tranid order by extract_dt) as rn
from policy) AS D
where rn = 1

Do any of the tables or policy itself have any additional fancyness
(more delete cascade/restrict/no action), after delete triggers)?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab