How to tune a DELETE?

I've got a large table and a short list of tuples to delete from it. The large table has about 6 million rows, the short list is under one thousand, and of course is different each time.

Every query plan I've gotten has given the same query plan, which in effect scans the entire large table testing each row to see if it should be deleted. I'm wondering: is there a way to get it to turn this around and scan the short list, deleting from the large one as needed? I'm sure this would execute much faster because of the large difference in sizes.

The DELETE command:
explain plan set statement_id='KEVIN.test_del1' for

        delete  /*+ CHOOSE */
        from ware1 ww
        where exists (select '1' from
                 delta_1_1 dd
              where
                ww.w0_supp_nation=dd.w0_supp_nation and
                ww.w0_cust_nation=dd.w0_cust_nation and
                ww.w0_shipmonth  =dd.w0_shipmonth   and
                ww.w0_ordermonth =dd.w0_ordermonth  and
                ww.w0_partkey    =dd.w0_partkey     and
                ww.w0_suppkey    =dd.w0_suppkey);

The primary key of both tables consists of exactly the columns named in the WHERE clause.

Here is the query plan I get:

OPERATION                OPTIONS     OBJECT_NAME   POSITION LEVEL


------------------------ ----------- ----------- ---------- -----


--DELETE STATEMENT Cost = 12642                       12642    1


----DELETE                           WARE1                1    2


------FILTER                                              1    3


--------TABLE ACCESS     FULL        WARE1                1    4


--------INDEX            UNIQUE SCAN P_DELTA_1_1          2    4

If it would only do a full scan on DELTA_1_1, and unique scan on WARE1, I could get the same result with one thousandth the number of unique scans... But can I get it to do this???             

--

Kevin O'Gorman (805) 650-6274 kogorman_at_pacbell.net At school: kogorman_at_cs.ucsb.edu
Permanent e-mail forwarder: Kevin.O'Gorman.64_at_Alum.Dartmouth.org Received on Wed Dec 08 1999 - 02:16:18 CST