Completed: (was: Tuning an UPDATE command)

From: Kevin O'Gorman <kevin_at_trixie.kosman.via.ayuda.com>
Date: Mon, 29 Nov 1999 12:37:54 -0800
Message-ID: <3842E422.850CFB99@trixie.kosman.via.ayuda.com>

One of the helpful people who got me on the right track asked for a final report, including full SQL and query plan.

The main trick was a complete rewrite of the SQL to avoid coordinated subqueries in favor of the UPDATE (<subquery>) SET... formulation. This produced about a 2-to-1 speedup.

The time got down to 238 seconds for the UPDATE alone, and if that represents the indexing from the small table into the large one, it's just 74 I/O for the full scan of the small table, and 5952 index lookups in the large table plus 5952 page reads for that large table. Call it 12000 page references and that's a pretty fast 50 I/O per second, or 20 ms each.

Not using the parallel option, and with 8K data blocks on top of a Linux filesystem, this may be about right for this iron, but then maybe I should be counting the re-writes of changed blocks too.

The SQL was:
update (
  select /*+ ordered */

     w.w0_supp_nation w_supp_nation,
     w.w0_cust_nation w_cust_nation,
     w.w0_shipmonth   w_shipmonth,
     w.w0_ordermonth  w_ordermonth,
     w.w0_partkey     w_partkey,
     w.w0_suppkey     w_suppkey,
     w.w0_quantity    w_quantity,
     w.w0_volume      w_volume,
     w.w0_cost        w_cost,
     w.w0_count       w_count,
     d.w0_supp_nation d_supp_nation,
     d.w0_cust_nation d_cust_nation,
     d.w0_shipmonth   d_shipmonth,
     d.w0_ordermonth  d_ordermonth,
     d.w0_partkey     d_partkey,
     d.w0_suppkey     d_suppkey,
     d.w0_quantity    d_quantity,
     d.w0_volume      d_volume,
     d.w0_cost        d_cost,
     d.w0_count       d_count
  from
     DELTA_&&many!_&&how d,
     WARE&&many w
  where
     w.w0_supp_nation = d.w0_supp_nation and
     w.w0_cust_nation = d.w0_cust_nation and
     w.w0_shipmonth = d.w0_shipmonth     and
     w.w0_ordermonth = d.w0_ordermonth   and
     w.w0_partkey = d.w0_partkey         and
     w.w0_suppkey = d.w0_suppkey

  )
set

  w_quantity = w_quantity + d_quantity,
  w_volume   = w_volume   + d_volume,
  w_cost     = w_cost     + d_cost,
  w_count    = w_count    + d_count;

and the associated plan for the main test run was:

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

Original posting:
Kevin O'Gorman wrote:
>
> 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 Mon Nov 29 1999 - 14:37:54 CST