Re: How to tune a DELETE?

Contrary to popular belief, IN subqueries can be very efficient. Your desired execution path can be achieved by:

delete /*+ index (ww) */
from ware ww
where (

                 ww.w0_supp_nation,
                 ww.w0_cust_nation,
                 ww.w0_shipmonth,
                 ww.w0_ordermonth,
                 ww.w0_partkey,
                 ww.w0_suppkey
        ) in
        (select
                dd.w0_supp_nation,
                dd.w0_cust_nation,
                dd.w0_shipmonth,
                dd.w0_ordermonth ,
                dd.w0_partkey,
                dd.w0_suppkey
        from delta_1_1 dd

The hint is probably redundant - it was on the test run I set up - but depends to some degree on a good index index existing on at least some of the WARE1 columns.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Kevin O'Gorman wrote in message
<384E1AEC.2615721F_at_trixie.kosman.via.ayuda.com>...
>Maybe I should add that I had a similar problem with UPDATE for exactly the
>same table, and got wonderful responses here that aimed me at a different
>syntax: update(<subquery>) SET.... rather than coordinated subqueries.
>This worked wonders, with about a 3x speedup. Unfortunately, I don't see
>an opportunity for the same trick here, because I don't want to delete from
>DELTA_1_1, so I dare not name it in the delete list...
>
>Kevin O'Gorman wrote:
>>
>> 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.
>>
Received on Wed Dec 08 1999 - 03:06:19 CST