Re: How to tune a DELETE?

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.
>
> 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

--
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:46:36 CST