Re: Tuning an UPDATE command

This was a modest win. Time for the installation phase dropped 25% from 661 to 498 seconds. Now I have to attack the delete that's using 180 of the remaining seconds -- an index will probably help.

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 - 14:06:24 CST