Re: Tuning an UPDATE command

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 27 Nov 1999 10:58:41 -0000
Message-ID: <943700419.16604.0.nnrp-04.9e984b29@news.demon.co.uk>

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
Received on Sat Nov 27 1999 - 04:58:41 CST