Re: query optimization

What's the SQL statement that you are using for this?

If you haven't already, I'd probably try a correlated subquery and scan through table A, like this:

delete from a
where not exists (select 'x' from b where a.id=b.id);  

Keep in mind that if you are deleting 70% of the table, you will probably have an issue with the highwatermark. Future scans of table A will have to scan through all of the database blocks up to the highwatermark. You can reset the highwatermark through a drop and create, or a truncate.

Rob Spies
rspies_at_minn.net

Karen Kluge <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 using the rule-based optimizer. Would a
>hint help me?
 

>TIA,
>Karen Kluge
>UC Davis
>Davis, California
Received on Thu Aug 14 1997 - 00:00:00 CDT