By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,749 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,749 IT Pros & Developers. It's quick & easy.

TIps on Delete Cascade performance

P: n/a
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!

Apr 20 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Apr 20 '06 #2

P: n/a
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


Apr 21 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.