delete based on multipart key
What's the right way to delete rows from a table which match a
multi-part key? I've got a table with a two part key and a unique
composite index on the key columns. I can't figure out an efficient way
to delete all the rows whose keys appear in a second table. That is, if
the main table contains these rows
column a column b
-------- --------
1 a
1 b
2 a
2 b
and the second table contains
1 a
2 b
I want to be left with
1 b
2 a
Here's a more complete contrived example, but this grew out of a real
application.
Take a table like this
create table multikey (
a int not null,
b int not null
);
create unique index i_multikey_1 on multikey (a, b);
and populate it with 100_000 rows with each key between 1 and 1000
perl -we '
srand;
%saw = ();
$got = 0;
while ($got < 100_000) {
$a = 1 + int rand 1000;
$b = 1 + int rand 1000;
next if $saw{$a, $b}++;
$got++;
print "$a|$b|\\n";
}' > data
echo "
begin work;
lock table multikey in share mode;
load from data insert into multikey; commit work;" | isql whatever -
rm data
Now say you need to delete a given number of these rows. I'll pick a
random 1%, but the idea is to be given a table which contains the
multipart keys which need to be removed.
select a, b
from multikey
-- Why doesn't "mod(rowid, 100) = 0" work?
where (rowid - 100 * trunc(rowid/100)) = 0
into temp deleteme with no log;
How do you delete from multikey all the rows in deleteme? Creating a
new index on multikey is no fair, it would be prohibitively expensive in
the production environment (plus the existing index is good for all
other uses of the table, it would be nasty to have to maintain a second
index just for deleting rows).
It seems like such a straightforward problem, but I haven't been able to
figure out a good solution. I've gotten solutions which work but are
slow (doing a correlated subquery). There must be a better way.
I'm using SE 4.10.UE2 with isql 4.10.UD2.
--
Roderick Schertler
roderick@argon.org