Re: Tuning an UPDATE command

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
Received on Sat Nov 27 1999 - 02:12:08 CST