Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Tuning an UPDATE command

Re: Tuning an UPDATE command

From: Kevin O'Gorman <kevin_at_trixie.kosman.via.ayuda.com>
Date: Sat, 27 Nov 1999 02:25:58 -0800
Message-ID: <383FB1B6.8FDF6C1@trixie.kosman.via.ayuda.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US