Re: query optimization

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/08/13
Message-ID: <33F2733D.6E4A@postoffice.worldnet.att.net>#1/1

It is not clear to me from your question if you drop the index on the ID column (at least in table A) before the DELETE operation. It could be more harmful than useful. If not, you could try these steps:

1. Drop the index on column ID in table A.
2. Run the following DELETE statement (or a similar one):

DELETE FROM a
WHERE id IN
(SELECT /*+ FULL(a) PARALLEL(a, n) */ id  FROM a
 MINUS
 SELECT /*+ FULL(b) PARALLEL(b, n) */ id  FROM b
);

where n is an integer representing the degree of parallelism you want for the SELECTs in your subquery.

3. After the DELETE, re-create the index you dropped in Step 1. Use CREATE INDEX with the UNRECOVERABLE clause to speed things up, although, since you have fewer rows in table A, the index should be created really fast.

Hope this helps.

Michael Serbanescu

---

Karen Kluge 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 Wed Aug 13 1997 - 00:00:00 CDT