473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Simple SQL question

I want to delete some rows from a table (TAB1), but only those rows which
has a match in antother table (TAB2). Something like this:

TAB1: col1, col2, .....
TAB2, col1, col2,......
There are indexes on col1 in both the tables.

The sql would be something like this

delete from tab1
where col1 in (select col1 from TAB2)

when I run this it takes 'for ever' to finish. I have tried to rewrite this
but cannot find a solution that runs fast.

There are 1.5 million rows in TAB1 and 50000 rows in TAB2. And , yes, i have
run runstats on both tables.

Anyone got a good solution to this?

Regards
Odd A
Aug 18 '06 #1
9 2379
Odd Bjørn Andersen wrote:
I want to delete some rows from a table (TAB1), but only those rows which
has a match in antother table (TAB2). Something like this:

TAB1: col1, col2, .....
TAB2, col1, col2,......
There are indexes on col1 in both the tables.

The sql would be something like this

delete from tab1
where col1 in (select col1 from TAB2)

when I run this it takes 'for ever' to finish. I have tried to rewrite this
but cannot find a solution that runs fast.

There are 1.5 million rows in TAB1 and 50000 rows in TAB2. And , yes, i have
run runstats on both tables.

Anyone got a good solution to this?
I don't know why the DELETE is misbehaving without seeing the plan.
But you can try this:
MERGE INTO TAB1 USING (SELECT DISTINCT c1 FROM TAB2) AS TAB1
ON TAB1.c1 = TAB2.c1
WHEN MATCHED DELETE

How many rows do you expect to be deleted?
If c1 is unique it should be limited to 50000, which is nothing.
But if c1 is not unique it depends on how you define "forever"

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 18 '06 #2

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:4k************@individual.net...
Odd Bjørn Andersen wrote:
>I want to delete some rows from a table (TAB1), but only those rows which
has a match in antother table (TAB2). Something like this:

TAB1: col1, col2, .....
TAB2, col1, col2,......
There are indexes on col1 in both the tables.

The sql would be something like this

delete from tab1
where col1 in (select col1 from TAB2)

when I run this it takes 'for ever' to finish. I have tried to rewrite
this but cannot find a solution that runs fast.

There are 1.5 million rows in TAB1 and 50000 rows in TAB2. And , yes, i
have run runstats on both tables.

Anyone got a good solution to this?
I don't know why the DELETE is misbehaving without seeing the plan.
But you can try this:
MERGE INTO TAB1 USING (SELECT DISTINCT c1 FROM TAB2) AS TAB1
ON TAB1.c1 = TAB2.c1
WHEN MATCHED DELETE

How many rows do you expect to be deleted?
If c1 is unique it should be limited to 50000, which is nothing.
But if c1 is not unique it depends on how you define "forever"
c1 is unique in TAB1 (primary key), but not in TAB2. I expect a little less
than 50000 should be deleted - don't have the exact number but it will be
approximately 45000, which as you say is nothing. I have tried this today
and it ran for 4 hours before I cancelled the sql......

I will try your suggestion and see what happens.

I try this now in a test environment, but when time comes to run this in
production there will be almost 1 million rows to delete. So I must get a
better performance first.

Regards
Odd A




Aug 18 '06 #3
Odd Bjørn Andersen wrote:
"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:4k************@individual.net...
>Odd Bjørn Andersen wrote:
>>I want to delete some rows from a table (TAB1), but only those rows which
has a match in antother table (TAB2). Something like this:

TAB1: col1, col2, .....
TAB2, col1, col2,......
There are indexes on col1 in both the tables.

The sql would be something like this

delete from tab1
where col1 in (select col1 from TAB2)

when I run this it takes 'for ever' to finish. I have tried to rewrite
this but cannot find a solution that runs fast.

There are 1.5 million rows in TAB1 and 50000 rows in TAB2. And , yes, i
have run runstats on both tables.

Anyone got a good solution to this?
I don't know why the DELETE is misbehaving without seeing the plan.
But you can try this:
MERGE INTO TAB1 USING (SELECT DISTINCT c1 FROM TAB2) AS TAB1
ON TAB1.c1 = TAB2.c1
WHEN MATCHED DELETE

How many rows do you expect to be deleted?
If c1 is unique it should be limited to 50000, which is nothing.
But if c1 is not unique it depends on how you define "forever"

c1 is unique in TAB1 (primary key), but not in TAB2. I expect a little less
than 50000 should be deleted - don't have the exact number but it will be
approximately 45000, which as you say is nothing. I have tried this today
and it ran for 4 hours before I cancelled the sql......

I will try your suggestion and see what happens.

I try this now in a test environment, but when time comes to run this in
production there will be almost 1 million rows to delete. So I must get a
better performance first.
If the MERGE gives you any trouble please post the db2exfmt output:
db2 "EXPLAIN PLAN FOR MERGE ...."

and then:
db2exfmt -d <db-o merge.exfmt -1

run sqllib/misc/EXPLAIN.DDL if EXPLAIN PLAN complains about lack of the
explain tables

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 18 '06 #4
Odd Bjørn Andersen wrote:
c1 is unique in TAB1 (primary key), but not in TAB2. I expect a little less
Besides what Serge suggested, other things to check would be to see if
the table has any large child tables with cascade delete relationship
(specially without supporting index), and/or delete triggers. These
don't show up in explain, but do have tangible overhead.

Regards

P Adhia
Aug 18 '06 #5
"Odd Bjørn Andersen" <ob****@online.nowrote in message
news:44***********************@news.sunsite.dk...
>I want to delete some rows from a table (TAB1), but only those rows which
has a match in antother table (TAB2). Something like this:

TAB1: col1, col2, .....
TAB2, col1, col2,......
There are indexes on col1 in both the tables.

The sql would be something like this

delete from tab1
where col1 in (select col1 from TAB2)

when I run this it takes 'for ever' to finish. I have tried to rewrite
this but cannot find a solution that runs fast.

There are 1.5 million rows in TAB1 and 50000 rows in TAB2. And , yes, i
have run runstats on both tables.

Anyone got a good solution to this?

Regards
Odd A
I am not 100 % sure, but I think this may be what you want:

delete from tab1 A
where col1 in (select B.col1 from TAB2 B where B.col1 = A.col1)

If the above is not correct, this may be what you want:

delete from tab1 A
where col1 in (select distinct col1 from TAB2)

Aug 18 '06 #6
How about this way?

delete from tab1
where EXISTS(SELECT * WHERE tab1.col1 = TAB2.col1 );

And make Index on TAN2.
CREATE INDEX Tab2_col1_IX ON TAB2 (col1);
(Oh, you wrote already that you made index on col1, This may be not
necessary.)

Aug 19 '06 #7

"P Adhia" <pa****@spamnot.yahoo.comwrote in message
news:53***************************@ALLTEL.NET...
Odd Bjørn Andersen wrote:
>c1 is unique in TAB1 (primary key), but not in TAB2. I expect a little
less

Besides what Serge suggested, other things to check would be to see if the
table has any large child tables with cascade delete relationship
(specially without supporting index), and/or delete triggers. These don't
show up in explain, but do have tangible overhead.

Regards

P Adhia
Thanks for all the response, but none have helped so far. There is something
strange going on here and I don't think it's the SQL in itself that causes
the problems.

It seems that the job stops after deleting some of the rows, and then
nothing seems to happen. The job does not continue or finish.

In TAB1 there are 1680554 rows, and I tried with 9465 rows (9295 unique
values in col1) in TAB2. When I tried the delete command like this:

delete from TAB1
where col1 in (select distinct col1 from TAB2)

the jobs deleted 308 rows and then STOPPED!

When I tried the merge command:

MERGE INTO TAB1 USING (SELECT DISTINCT col1 FROM TAB2) AS TAB2
ON TAB1.col1= TAB2.col1
WHEN MATCHED THEN DELETE

Then the job deleted 9244 rows and then stopped.

In both circumstances it took less than a second before the jobs stopped.

There are RI in question here, but I have deleted all the records in all the
underlying tables to TAB1 (just checked !!). So there should be no overhead
with delete cascade.

I set diaglevel to 4 to see if there are any messages concerning this, but
the only thing I got was:

2006-08-21-10.43.03.440000+120 E18248H389 LEVEL: Info (OS)
PID : 3960 TID : 1052 PROC : db2cmnclp.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, oper system services, sqlodelete, probe:100
CALLED : OS, -, unspecified_system_function
OSERR : 2 "The system cannot find the file specified."
DATA #1 : File name, 6 bytes
\s3ro.

but I cannot see that this has any relevance to my problem.

So now I am even more confused, if possbile, than when I started out.....

Odd B
Aug 21 '06 #8
Could it be DB2 is waiting on a lock?
Try db2pd an see what it reports for your application id.
(Figuring out db2pd shall be homework ;-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 21 '06 #9
Odd Bjørn Andersen wrote:
It seems that the job stops after deleting some of the rows, and then
nothing seems to happen. The job does not continue or finish.
That is indeed strange. Could it be that DB2 is trying to allocate
secondary logs and that is taking too long for some reasons? Try (of
course not in prod) setting NOT LOGGED INITIALLY attribute for the
table and see if that helps.

P Adhia

Aug 22 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Patchwork | last post by:
Hi Everyone, Please take a look at the following (simple and fun) program: //////////////////////////////////////////////////////////////////////////// ///////////// // Monster Munch, example...
1
by: Proteus | last post by:
Any help appreciated on a small perl project I need to write for educator/teaching purposes. I have not programmed perl for some time, need to get up to speed, maybe some kind souls hrere will help...
2
by: Raskolnikow | last post by:
Hi! I have a very simple problem with itoa() or the localtime(...). Sorry, if it is too simple, I don't have a proper example. Please have a look at the comments. struct tm *systime; time_t...
3
by: Peter | last post by:
Hello Thanks for reviewing my question. I would like to know how can I programmatically select a node Thanks in Advanc Peter
7
by: abcd | last post by:
I am trying to set up client machine and investigatging which .net components are missing to run aspx page. I have a simple aspx page which just has "hello world" printed.... When I request...
4
by: dba_222 | last post by:
Dear Experts, Ok, I hate to ask such a seemingly dumb question, but I've already spent far too much time on this. More that I would care to admit. In Sql server, how do I simply change a...
14
by: Giancarlo Berenz | last post by:
Hi: Recently i write this code: class Simple { private: int value; public: int GiveMeARandom(void);
30
by: galiorenye | last post by:
Hi, Given this code: A** ppA = new A*; A *pA = NULL; for(int i = 0; i < 10; ++i) { pA = ppA; //do something with pA
10
by: Phillip Taylor | last post by:
Hi guys, I'm looking to develop a simple web service in VB.NET but I'm having some trivial issues. In Visual Studio I create a web services project and change the asmx.vb file to this: Imports...
17
by: Chris M. Thomasson | last post by:
I use the following technique in all of my C++ projects; here is the example code with error checking omitted for brevity: _________________________________________________________________ /*...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.