Re: Oracle's update statement

tonixlaw_at_gmail.com wrote:
> Hi all,
>
> I am trying to partially update a table based on a heavy query.
>
> If I use following update:
>
> update
> table_A
> set
> (ColA, ColB) =
> (select ColA, ColB
> from heavy_query_B alias_B
> where id = table_A.id)
> where
> exists
> (select *
> from heavy_query_B alias_B
> where id = table_A.id)
>
> It would be extremely slow. The "where" clause is required to avoid
> updating unwanted rows in table_A, however i think it's one of the
> problems that impact performance.
>
> Any idea that could help?
>
> Thanks a lot!

Are you attempting to tune one statement, or are you starting to learn how to tune SQL in general?

Its helpful to provide information such as Oracle edition, version. Sometimes the patchset that is being used matters, as well as the server OS.
Posting an explain plan would also probably attract more attention. Sometimes the exercise of putting together the test case causes the problem or solution to leap off the monitor at you. Have statistics been gathered for the tables and indexes? Were histograms gathered?
What initialization parameters have been set to non-default values?

GUI tools exist for providing tuning methodologies via an "expert system" or monte carlo simulation whereby you don't have to be down and dirty in 10046 and 10053 trace land in udump.

Perhaps using the SQL Tuning Advisor provided with Oracle Enterprise Manager might be a good way to go - if you're properly licensed for it.

hth.

-bdbafh Received on Fri Nov 03 2006 - 11:12:24 CST