Delete explain plan question - DB2 V8 and V9

[dunleav1]

Here's the statement: delete from table a where id1=4;

Table a is a parent to table b,c,d,e,f,g,h,i,j,k,l
Table a key is id1,id2
table b,c,d,e,f,g,h,i,j,k,l key is id2,id3.

I delete all the rows from b,c,d,e,f,g,i,j,k,l before deleting the
rows from table a.

The explain plan shows the following:
table a
delete timerons 5,862,065
fetch timerons 49,609
ridscan timerons 26,354
sort timerons 26,354
ixscan 25,824
index on table a -

then for table b,c,d,e,f,g,h,i,j,k,l - and ixscan for each table with
low timerons.
table timeron
a 11
b 17
c .01
d .01
e .01
f .01
g .01
h 11
i .01
j .01
k 11
l 11

Statictics are up to date.

Why are the timerons on table a so high ?
I deleting a million rows of data but I wouldn't expect to take so
long as it is.

[dunleav1]

I HAD AN ERROR IN THE FIRST POST - USE THIS ONE.

Here's the statement: delete from table a where id3=4;

Table a is a parent to table b,c,d,e,f,g,h,i,j,k,l
Table a key is id1,id2
table a index2 is id3,id1

table b,c,d,e,f,g,h,i,j,k,l key is id2,id3.

I delete all the rows from b,c,d,e,f,g,i,j,k,l before deleting the
rows from table a.

The explain plan shows the following:
table a
delete timerons 5,862,065
fetch timerons 49,609
ridscan timerons 26,354
sort timerons 26,354
ixscan on index2 25,824
index2 on table a -

then for table b,c,d,e,f,g,h,i,j,k,l - and ixscan for each table with
low timerons.
table timeron
a 11
b 17
c .01
d .01
e .01
f .01
g .01
h 11
i .01
j .01
k 11
l 11

Statictics are up to date.

Why are the timerons on table a so high ?
I deleting a million rows of data but I wouldn't expect to take so
long as it is.

[dunleav1]

On Mar 14, 4:30 pm, "dunleav1" <[e-mail]:

I HAD AN ERROR IN THE FIRST POST - USE THIS ONE.

Here's the statement: delete from table a where id3=4;

Table a is a parent to table b,c,d,e,f,g,h,i,j,k,l
Table a key is id1,id2
table a index2 is id3,id1

table b,c,d,e,f,g,h,i,j,k,l key is id2,id3.

I delete all the rows from b,c,d,e,f,g,i,j,k,l before deleting the
rows from table a.

The explain plan shows the following:
table a
delete timerons 5,862,065
fetch timerons 49,609
ridscan timerons 26,354
sort timerons 26,354
ixscan on index2 25,824
index2 on table a -

then for table b,c,d,e,f,g,h,i,j,k,l - and ixscan for each table with
low timerons.
table timeron
a 11
b 17
c .01
d .01
e .01
f .01
g .01
h 11
i .01
j .01
k 11
l 11

Statictics are up to date.

Why are the timerons on table a so high ?
I deleting a million rows of data but I wouldn't expect to take so
long as it is.

Dbadvis makes a few index suggestions but nothing that will change the
explain plan dramatically.
The server is churning on CPU - 25%.
DB snapshots really don't show much.
The activity monitors show for the delete statement:
rows read 952,236
rows written 1,670,236
user cpu 67,546,390,625
**********************YIKES
system cpu 181,531,250

What could be causing this huge USER CPU allocation?

[Ian]

dunleav1 wrote:

I HAD AN ERROR IN THE FIRST POST - USE THIS ONE.

Here's the statement: delete from table a where id3=4;

Table a is a parent to table b,c,d,e,f,g,h,i,j,k,l
Table a key is id1,id2
table a index2 is id3,id1

table b,c,d,e,f,g,h,i,j,k,l key is id2,id3.

I delete all the rows from b,c,d,e,f,g,i,j,k,l before deleting the
rows from table a.

It would be more helpful if you could post the actual plan
instead of trying to describe it.

Also, if you are going to delete the child rows first, why are all of
the foreign keys defined as delete cascade?