Re: Tuning an UPDATE command

From: Kevin O'Gorman <kevin_at_trixie.kosman.via.ayuda.com>
Date: Sat, 27 Nov 1999 09:53:07 -0800
Message-ID: <38401A83.58312201@trixie.kosman.via.ayuda.com>

Jonathan, I think you've got it. At least the plan explanation is right. It will take several hours for the test runs to complete (the large table and index need to be rebuilt).

The explained plan is now cost 5964 (down from 12642), so the move is in the right direction. I had been hoping for and even bigger drop, but I'll take what I can get. The explanation is now:

OPERATION                       OPTIONS         OBJECT_NAME POSITION

LEVEL

------------------------------- --------------- ----------- --------
-----
--UPDATE STATEMENT Cost = 5964                                  5964    
1
----UPDATE                                      WARE1              1    
2
------NESTED LOOPS                                                 1    
3
--------TABLE ACCESS            FULL            DELTA_1_1          1    
4
--------TABLE ACCESS            BY INDEX ROWID  WARE1              2    
4
----------INDEX                 UNIQUE SCAN     PK_WARE1           1    

5

6 rows selected.

The new SQL (in all its glory) is:

SQL> explain plan set statement_id='&&part' for   2 update (
  3 select /*+ ordered */

  4       w.w0_supp_nation w_supp_nation,
  5       w.w0_cust_nation w_cust_nation,
  6       w.w0_shipmonth   w_shipmonth,
  7       w.w0_ordermonth  w_ordermonth,
  8       w.w0_partkey     w_partkey,
  9       w.w0_suppkey     w_suppkey,
 10       w.w0_quantity    w_quantity,
 11       w.w0_volume      w_volume,
 12       w.w0_cost        w_cost,
 13       w.w0_count       w_count,
 14       d.w0_supp_nation d_supp_nation,
 15       d.w0_cust_nation d_cust_nation,
 16       d.w0_shipmonth   d_shipmonth,
 17       d.w0_ordermonth  d_ordermonth,
 18       d.w0_partkey     d_partkey,
 19       d.w0_suppkey     d_suppkey,
 20       d.w0_quantity    d_quantity,
 21       d.w0_volume      d_volume,
 22       d.w0_cost        d_cost,
 23       d.w0_count       d_count
 24    from
 25       DELTA_&&many!_&&how d,
 26       WARE&&many w
 27    where
 28       w.w0_supp_nation = d.w0_supp_nation and
 29       w.w0_cust_nation = d.w0_cust_nation and
 30       w.w0_shipmonth = d.w0_shipmonth     and
 31       w.w0_ordermonth = d.w0_ordermonth   and
 32       w.w0_partkey = d.w0_partkey         and
 33       w.w0_suppkey = d.w0_suppkey

 34 )
 35 set

 36    w_quantity = w_quantity + d_quantity,
 37    w_volume   = w_volume   + d_volume,
 38    w_cost     = w_cost     + d_cost,
 39    w_count    = w_count    + d_count;
old   1: explain plan set statement_id='&&part' for
new   1: explain plan set statement_id='KEVIN.APPLY.1.1.update7' for
old  25:      DELTA_&&many!_&&how d,
new  25:      DELTA_1_1 d,
old  26:      WARE&&many w
new  26:      WARE1 w

Explained.                   

Jonathan Lewis wrote:
>
> update (
> select /*+ ordered index(large) */
> large_columns_including large_pk,
> small_columns
> from
> small,
> large
> where
> large.pkcols = small.pk_cols
> ) v
> set v.large_columns = v.small_columns
> ;
>
> Should work because of the primary key restrictions.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Kevin O'Gorman wrote in message
> <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 - 11:53:07 CST