Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Tuning an UPDATE command
Replacing * with 'x' helped a little, but not much because that was the
scan of
the smaller table anyway. It's a good idea though. I could see no
change from
using PUSH_SUBQ.
Here's the full SQL
SQL> explain plan set statement_id='&&part' for
2 update /*+ PUSH_SUBQ */
3 WARE&&many w
4 set (w0_quantity, w0_volume, w0_cost, w0_count) =
5 (select 6 w.w0_quantity + dd.w0_quantity, 7 w.w0_volume + dd.w0_volume, 8 w.w0_cost + dd.w0_cost, 9 w.w0_count + dd.w0_count 10 from DELTA_&&many!_&&how dd 11 where 12 w.w0_supp_nation = dd.w0_supp_nation 13 and w.w0_cust_nation = dd.w0_cust_nation 14 and w.w0_shipmonth = dd.w0_shipmonth 15 and w.w0_ordermonth = dd.w0_ordermonth 16 and w.w0_partkey = dd.w0_partkey 17 and w.w0_suppkey = dd.w0_suppkey 18 ) 19 where exists 20 (select 'x' from DELTA_&&many!_&&how d where 21 w.w0_supp_nation = d.w0_supp_nation 22 and w.w0_cust_nation = d.w0_cust_nation 23 and w.w0_shipmonth = d.w0_shipmonth 24 and w.w0_ordermonth = d.w0_ordermonth 25 and w.w0_partkey = d.w0_partkey 26 and w.w0_suppkey = d.w0_suppkey); old 1: explain plan set statement_id='&&part' for new 1: explain plan set statement_id='KEVIN.APPLY.1.1.update6' for old 3: WARE&&many w new 3: WARE1 w old 10: from DELTA_&&many!_&&how dd new 10: from DELTA_1_1 dd old 20: (select 'x' from DELTA_&&many!_&&how d where new 20: (select 'x' from DELTA_1_1 d where
and the explanation is (sorry about the word wrap)
OPERATION OPTIONS OBJECT_NAME POSITION LEVEL
---------------------------------------- --------------------
------------------
It's the FULL access of WARE1 that's causing my grief. I wish it were a
(fast) full scan of
DELTA_1_1 and an index lookup into WARE1. That would reduce operations
by three orders
of magnitude.
Both tables have a primary key made up of the six attributes that are compared.
Sybrand Bakker wrote:
> > First of all replace the * by 'x' in the exists subquery. > The * will force Oracle to retrieve the datablock, the 'x' will use the > index only. > Then Oracle 8i should have a hint (AFAIK pushq) to make sure the subquery is > processed first. > If you have any results to share, please post the full sql along with the > explain plan results. > > Hth, > > -- > Sybrand Bakker, Oracle DBA > Kevin O'Gorman <kevin_at_trixie.kosman.via.ayuda.com> wrote in message > news:383F80FA.E9258CCD_at_trixie.kosman.via.ayuda.com... > > I've got a large table being updated by the contents of a small one, > > and I can't seem to get a reasonable execution plan out of Oracle 8i. > > > > The large table (about 6 million rows) has a primary key with 6 fields. > > The small table (about 5 thousand rows) has an identical primary key. > > The query plans always do a full scan of the large table, and access the > > small table by its key. That's 6 million accesses. I want a plan that > > does it the other way around, which would be 5 thousand index accesses. > > > > I've tried all sorts of ANALYZE commands, with no change in query plan. > > I'm about to explore alternative SQL formulations, but I'm floundering. > > > > Right now the SQL looks like this: > > > > update LARGE L > > set (attr1, attr2, ... ) = > > ( select (L.attr1 + S.attr1, -- just adding fields from SMALL > > into LARGE > > L.attr2 + S.attr2, > > ... > > from SMALL S > > where > > L.key1 = S.key1 and > > L.key2 = S.key2 and > > ... > > ) > > where exists > > (select * from SMALL S > > where > > L.key1 = S.key1 and -- same index list as the first subquery. > > L.key2 = S.key2 and > > ... > > ); > > > > > > The question is: is there a way to force Oracle 8i to drive > > the update by the SMALL table rather than the large one? It > > cannot seem to figure that out on its own, even with table > > statistics showing a 1000-to-1 difference in the cardinalities. > > > > Thanks in advance, > > > > --kevin > > -- > > 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 Sat Nov 27 1999 - 04:25:58 CST