467,149 Members | 1,343 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

Delete explain plan question - DB2 V8 and V9


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.

Mar 14 '07 #1
  • viewed: 3836
Share:
3 Replies
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.

Mar 14 '07 #2
On Mar 14, 4:30 pm, "dunleav1" <jmdunle...@comcast.netwrote:
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?

Mar 15 '07 #3
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?

Mar 18 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Ina Schmitz | last post: by
14 posts views Thread by Michel Esber | last post: by
reply views Thread by JAW | last post: by
3 posts views Thread by raviva | last post: by
8 posts views Thread by Michel Esber | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.