a) I wasn't suggesting that you use an updatable join view, but I can see
how you could misinterpret what I said. The idea was to see if you could
write a single subquery that joined several of the source tables (such as
PRODUCT_TAB) and then still carry out that full tablescan of the GTT but
using that join as your subquery update rather than having "one tablescan
and update = one subquery".
I see you're already doing something of this kind using the MERGE command;
if you're getting an ORA-01779 when you try to rewrite one of your updates
as an update through an updatable join view this means that equivalent code
to do a MERGE could produce a run-time error ORA-30926). The update portion
of a merge and an updatable join view both require the same uniqueness but
the view enforces the logical requirement at compile time while the merge
command allows you to get away with the update so long as you get lucky
with the data.
I note that you still haven't moved the "tmp.cind = 'Y'" predicate to the
correct position - that should have been the zeroth step before worrying
about anything else. I assume the one update I picked out isn't the only
one where this error occurs.
b) Correct regarding multiple GTTs. Each "insert /*+ append */" would
probably require it's own target GTT. Remember, though, that I haven't
examined your original posting in detail, and you probably haven't told us
everything a consultant would ask about so if your current code has some
parts that update disjoint subsets of the data you might find parts of the
rewrite where you could do multiple inserts into the same GTT.
One thing to bear in mind - a possible bar to adopting this approach - is
that you would have to commit after insert otherwise the next insert, or
the next query against the target GTT would raise the (unexpectedly
parallel) error: ORA-12838: cannot read/modify an object after modifying
it in parallel
Regards
Jonathan Lewis
On Mon, 3 May 2021 at 12:58, 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?
