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

Delete explain plan question - DB2 V8 and V9

P: n/a

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
Share this Question
Share on Google+
3 Replies


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

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

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