The ORA-01779 error means you would need a unique index or constraint on
product_tab (part_dt, tid) to be able to use this syntax. (Strangely, I did not
get the error in 21c. Maybe there is a new feature I am missing.)
You may be able to combine the updates into a smaller number of merges even
when different rules apply, using CASE expressions so that each column is only
set to a new value when the relevant conditions are met, otherwise set it to
itself.
when matched then update
set tmp.c1 = case when x = y then p.col1 else tmp.c1 end
, tmp.c2 = case when a = b then p.col2 else tmp.c2 end;
William
On 3 May 2021, at 12:57, Lok P <loknath.73@xxxxxxxxx> wrote:
Thank You Jonathan and Lothar. I was trying to modify one of the sample
updates(as below) as per your suggested method.
UPDATE GTT_TAB TMP
SET (c1, c2, c3, c4,c5.. c11) =
( (SELECT col1,col2,..col11
FROM PRODUCT_TAB P
WHERE P.TID = TMP.TID
AND TMP.CIND = 'Y'
AND P.PART_DT = TMP.PART_DT
AND P.CODE = 'XX'))
Jonathan, let me know if my understanding is correct on the suggested points
" a) See if you can minimise the number of update ste[ps by updating from a
JOIN of several source tables"
So if I get the above point correct then I was trying to modify the UPDATE as
below , but I am getting ORA-01779 while running to see the plan. So does it
mean that the GTT has to have a unique key present in it to have this method
work?
UPDATE (SELECT P.COL1...P.col11,TMP.C1.. TMP.C11
FROM PRODUCT_TAB P,GTT_TAB TMP
WHERE P.TID = TMP.TID
AND TMP.CIND = 'Y'
AND P.PART_DT = TMP.PART_DT
AND P.CODE = 'XX'
) SET C1=COL1, ... C11=COL11;
ERROR at line 10:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Regarding below point ,
"b) Rewrite the code to step through a series of GTT doing
insert /*+ append */ into next_gtt select from previous_gtt join
{source tables}"
Do you mean i have to replace the UPDATE with INSERT /*+APPEND*/... queries but
as it cant be done in the same GTT , so i have to create multiple GTT's for
each UPDATES, so as to replace them with INSERT APPEND queries?
Lothar , as you suggested , i tried modifying the same above query as below
MERGE statement. Though i was not able to see the plan on production because
its a DML, so i tried running it on DEV and as the data volume was not same so
with some force cardinality hints, i do see a HASH JOIN operation, but not
seeing bloom filter right away. Maybe , we will see it in prod. And also i
remember , there was a bug on 11.2.0.4 which was restricting the cell smart
scan in case of DML queries and work around for that was to set
_serial_direct_read=always. Not sure if we have to do that here in case of
MERGE statement to go for cell offloading. Also if it's worth modifying each of
these 4-5 different UPDATES to one MERGE statement each? I was thinking if
combining them to one is possible and will give benefit.
MERGE INTO GTT_TAB TMP
USING (SELECT col1,col2,..col11,CODE, PART_DT,TID
FROM PRODUCT_TAB P WHERE P.CODE = 'XX')P
On ( P.TID = TMP.TID AND P.PART_DT = TMP.PART_DT )
WHEN MATCHED THEN UPDATE
SET TMP.C1=P.COL1,
...
TMP.C11=P.col11
where TMP.CIND = 'Y'
Execution Plan
----------------------------------------------------------
Plan hash value: 2005806797
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
|TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 1339 |
| 256K (1)| 00:51:23 | | |
| 1 | MERGE | GTT_TAB | | |
| | | | |
| 2 | VIEW | | | |
| | | | |
|* 3 | HASH JOIN | | 1 | 639 |
732M| 256K (1)| 00:51:23 | | |
| 4 | PARTITION RANGE ALL | | 8000K| 640M|
| 5894 (3)| 00:01:11 | 1 | 331 |
|* 5 | TABLE ACCESS STORAGE FULL| PRODUCT_TAB | 8000K| 640M|
| 5894 (3)| 00:01:11 | 1 | 331 |
| 6 | TABLE ACCESS STORAGE FULL | GTT_TAB | 8000K| 4234M|
| 206 (0)| 00:00:03 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TID"="TMP"."TID" AND "PART_DT"="TMP"."PART_DT")
5 - storage("P"."CODE"='XX')
filter("P"."CODE"='XX')
Regards
Pap
On Mon, May 3, 2021 at 2:25 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx
<mailto:jlewisoracle@xxxxxxxxx>> wrote:
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.
a) Updating lots of columns means you will probably start doing lots of row
migration - and you can't set pctfree on GTTs to avoid this, it WILL be 10%
b) You will be generating a huge amount of undo
c) If you aren't including a "where exists" predicate on the source table that
matches the predicate on the set subquery you will be updating rows to null
that don't need to be updated - the monitor report echoed by Mladen showed 17M
rows updated, but only 8M executions of the subquery - which probably means 9M
rows in the GTT updated to NULL.
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 -
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
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
b) miimise BOTH undo and redo.
Regards
Jonathan Lewis
On Sun, 2 May 2021 at 19:53, Lok P <loknath.73@xxxxxxxxx
<mailto:loknath.73@xxxxxxxxx>> wrote:
Hello, We have a plsql procedure in which it updates multiple columns of 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
