Tuning an UPDATE command

From: Kevin O'Gorman <kevin_at_trixie.kosman.via.ayuda.com>
Date: Fri, 26 Nov 1999 22:58:02 -0800
Message-ID: <383F80FA.E9258CCD@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 - 00:58:02 CST