Re: query optimization

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/08/14
Message-ID: <33F38727.3F5E@iol.ie>#1/1

Brenda Muller wrote:
>
> In article <33F23ACA.1359F04B_at_ucdavis.edu>,
> kakluge_at_ucdavis.edu wrote:
> >
> > I have one table (A) with ~30,000 rows in it and a second table (B) with
> > ~85,000 rows in it. Each table contains an ID column, and each table in
> > indexed on that ID column (these are not primary key type ID's; there
> > are duplicates, so the indexes are not unique). I want to delete from
> > table A all rows where the ID does not exist in table B. When I'm done,
> > I've deleted ~22,000 rows (>70% of the rows) from table A. The problem
> > is the query takes most of the day to run, so I'd like to optimize it.
> > Because this is a batch deletion I want best throughput rather than best
> > response time. As far as I know we don't generate analysis statistics,
> > so I believe this means we're u
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

I didn't see Brenda's response. Here's my offering:

drop index <name>; -- index on A(id)

delete from A	-- faster because no index to organise
where not exists	-- *definitely not* "where A.id not in (select B.id"

(select null from B
where B.id = A.id);

create index <name> on A(id);

This should run in reasonable time;

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"

Received on Thu Aug 14 1997 - 00:00:00 CDT