Re: Tuning Update query

So if I get it correct there are basically three approaches to handle this multiple UPDATE statement in a better way. Is it correct to first try the merge query, as the merge query has the option to combine multiple UPDATES to one using CASE statement. Then the updateable view approach but that will need additional index on the GTT_TAB on column PART_DT,TID. And as almost all the updates were having joins with the transaction table(PRODUCT_TAB) and GTT with these two columns, so that one index should suffice. Finally the INSERT APPEND approach which i am still confused a bit , if each of the Update will need a different GTT altogether which will be inserted into and of course a different INSERT statement?

Regards
Lok

On Mon, May 3, 2021 at 8:52 PM William Robertson < william_at_williamrobertson.net> wrote:

> Then in 10g everyone discovered the undocumented /*+ bypass_ujvc */ hint
> and started using it everywhere, until it quietly disappeared in 11g and
> they had to rewrite all their join view updates as merges or forall.
>
> William
>
> On 3 May 2021, at 16:02, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> 
> OMG! That was about time. I still remember spending hours to get it to
> work as young consultant.
>
> Am 03.05.2021 um 16:39 schrieb Jonathan Lewis:
>
> From the 21c Database Admin Guide:
>
> https://docs.oracle.com/en/database/oracle/oracle-database/21/admin/managing-views-sequences-and-synonyms.html#GUID-077BFD57-3F38-4487-8A92-93ACBB48E2EA
>
>
> *Starting with Oracle Database Release 21c, it is not mandatory for all
> updatable columns in a join view to map to columns of a key-preserved
> table. When updating a join view, ensure that the UPDATE operation is
> deterministic. *
>
> So the compile-time restriction has been removed and updates of join views
> are now consistent with the run-time behaviour of the MERGE command. i.e.
> it work until it breaks with the run-time error: ORA-30926: unable to get a
> stable set of rows in the source tables
>
> Regards
> Jonathan Lewis
>
>
> On Mon, 3 May 2021 at 15:11, William Robertson <
> william_at_williamrobertson.net> wrote:
>
>> 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.)
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l