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/