RESOLVED: How to tune a DELETE?

Bingo, Jonathan! Once again you save my bacon.

BTW, the hint was required; without it I get the exact query plan I got before.

Thanks again,
  --kevin

Jonathan Lewis wrote:
>
> 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.
> >>

--
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 - 11:25:22 CST