Huge estimated cost for a simple delete statement with cascade

[Kumar]

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 ?

[Mark]

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.

[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 ?

[Kumar]

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....