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

Huge estimated cost for a simple delete statement with cascade

P: n/a
Hi,

I have a table (TAB1) withi has 4 child tables on COL1 with DELETE
CASCADE.

When I delete any row in TAB1, it will delete corresponding rows on
all child tables.

SQL : delete from TAB1 where COL1 = 21

Explain shows the following :

Estimated Cost = 164650096.000000
Estimated Cardinality = 6440161.000000

Can anybody help me in tuning this please ?
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ku***@tumkurcity.com (Kumar) wrote in message news:<2f**************************@posting.google. com>...
Hi,

I have a table (TAB1) withi has 4 child tables on COL1 with DELETE
CASCADE.

When I delete any row in TAB1, it will delete corresponding rows on
all child tables.

SQL : delete from TAB1 where COL1 = 21

Explain shows the following :

Estimated Cost = 164650096.000000
Estimated Cardinality = 6440161.000000

Can anybody help me in tuning this please ?


Unless you run detailed stats against all the tables involved using
the runstats command, DB2 may not know how many rows match that value
in the child tables. Check the Command Reference manual to see what
syntax is needed for capturing the most detailed information possible
on the foreign key columns in the child tables.
Nov 12 '05 #2

P: n/a
Leo
Is col1 the primary key?
Are stats up to date?
Do you have indexes on the foreign keys?
Can you post the access plan and not just the cost estimates?
How many child records exists for each parent record?

ku***@tumkurcity.com (Kumar) wrote in message news:<2f**************************@posting.google. com>...
Hi,

I have a table (TAB1) withi has 4 child tables on COL1 with DELETE
CASCADE.

When I delete any row in TAB1, it will delete corresponding rows on
all child tables.

SQL : delete from TAB1 where COL1 = 21

Explain shows the following :

Estimated Cost = 164650096.000000
Estimated Cardinality = 6440161.000000

Can anybody help me in tuning this please ?

Nov 12 '05 #3

P: n/a
YES...COL1 is the primary key.
YES...STATS are up-to-date.
NO...not all tables ahve indexes defined on these foreign columns ,
however, there are only very few records on all tables..maximum 100
rows....
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.