Re: MERGE statement with parallel DML enabled deadlocks itself

From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Thu, 07 May 2015 09:03:43 +0200
Message-ID: <554B0E4F.3050203_at_mgm-tp.com>

Sayan Sergeevich Malakshinov schrieb am 06.05.2015 um 13:41:
> I think it would be better and more reliable to execute 33 merge statements in parallel sessions/jobs each by own partition, like this:
>
> merge into target_table partition(p1) tg
> using
> (
> select ...
> from base_table_1 partition(p1) bt
> left join data_table_1 partition(p1) dt
> on ...
> group by ...
> ) t on (t.part_key = tg.part_key and t.id1 = tg.id1 and t.id2 = tg.id2 and t.id3 = tg.id3)
> when matched then update
> set aggregated_col_1 = t.aggregate_col_1,
> aggregated_col_2 = t.aggregate_col_2,
> ...

Thanks for the suggestion.

We can't really change the SQL statements to include the partition names (that would require dynamic SQL and given the size of the statements this would not be feasible)

What we _can_ do though, is to include the partition key in the where clause (where part_key = 1). Due to partition pruning this should have the same effect, shouldn't it?

Regards
Thomas  

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

Received on Thu May 07 2015 - 09:03:43 CEST