Re: Tuning Update query
Date: Mon, 3 May 2021 09:55:12 +0100
Message-ID: <CAGtsp8krcrvK7uA_DnPd6HuCedWbp=AcMH=FWrC1UggXWRkN3g_at_mail.gmail.com>
If you're updating many columns of every row in a large (global temporary)
table using lots of separate statements that use the "set .. subquery"
strategy you are introducing 4 problems.
In your specific case there's (probably) a logic flaw in the update
statement. You have a "where tmp.cind='Y'" in the subquery. This means
(effectively) set the values to NULL when tmp.cind ='Y', NOT 'don't update
the rows where tmp.cind='Y'. If you want the update to be selective then
you need the predicate as part of the where clause on the update.
Finally -
insert /*+ append */ into next_gtt select from previous_gtt join
{source tables}
Using insert /*+ append */ with gtts will:
a) use bulk processing rather that the row by row processing for filter
subqueries
Regards
On Sun, 2 May 2021 at 19:53, Lok P <loknath.73_at_gmail.com> wrote:
> Hello, We have a plsql procedure in which it updates multiple columns of
If this is a table of roughly 17M rows (rather than the 8billion of the
estimate) then the fastest strategy would probably be to
a) See if you can minimise the number of update ste[ps by updating from a
JOIN of several source tables
b) Rewrite the code to step through a series of GTT doing
b) miimise BOTH undo and redo.
Jonathan Lewis
> the global temporary table after loading it in different steps. It's
> updated for different filter criteria and each of these UPDATES are running
> for hours even if they are simple ones. I am trying to see if it's
> possible to do it in an efficient fashion by combining all different
> updates into one UPDATE statement or any other possible method? One initial
> thought was to do it as part of the INSERT itself rather than updating it
> through multiple statements at the later stage , but then I see the data
> load/INSERT is happening for multiple if/else conditions which may not be
> straight enough.
>
> Attached is the sql monitor for few of the long running UPDATES. And i am
> seeing this is not really spending time for updating rows but rather in
> finding the eligible rows for update i.e. the SELECT part of it is actually
> taking time. So wanted to understand how these UPDATES can be done faster?
>
> Its version 11.2.0.4 of Oracle Exadata-X5.
>
> Regards
>
> Lok
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon May 03 2021 - 10:55:12 CEST