Another little tip for performance of a MERGE command. Particularly
relevant if it's an UPDATE-only.
Don't reference the underlying tables, replace each table (in the INTO and
USING clauses) with an inline view referencing only the columns you need
from the two tables, so:
merge into (select column list from table1) tab1
using (select column list from table2) tab2
on tab1.cola = tab2.cola .... etc,
See https://jonathanlewis.wordpress.com/2016/06/06/merge-precision/ for a
longer explanation
Regards
Jonathan Lewis
On Sat, 6 Nov 2021 at 08:39, Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
BTW: you should not use parallel(LOG,4) but just parallel(4).
Am 06.11.2021 um 04:20 schrieb Sayan Malakshinov:
Hi Amir,
ENABLE_PARALLEL_DML was introduced in oracle 12.1, so it doesn't work on
11.2.0.4
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE
http://orasql.org
сб, 6 нояб. 2021 г., 6:16 Hameed, Amir <amir.hameed@xxxxxxxxxxxxxxx>:
Hi,
The database version is 11.2.0.4. I am trying to modify a MERGE statement
by creating a SQL baseline and introducing parallelism through hints as
shown below:
-------------------------------------
MERGE /*+ enable_parallel_dml parallel(LOG,4) */ INTO CN_NOTIFY_LOG_ALL
LOG USING CN_PROCESS_BATCHES_ALL BATCH ON ( BATCH.PHYSICAL_BATCH_ID =
:B1 AND BATCH.ORG_ID = LOG.ORG_ID AND BATCH.SALESREP_ID =
LOG.SALESREP_ID AND LOG.PERIOD_ID BETWEEN BATCH.PERIOD_ID AND
BATCH.END_PERIOD_ID AND LOG.START_DATE >= BATCH.START_DATE) WHEN
MATCHED THEN UPDATE SET LOG.STATUS = 'COMPLETE' WHERE LOG.STATUS =
'INCOMPLETE'
;
When the statement is run through SQL*Plus via a script, parallelism is
working. However, it is not working when adding the above hints by using
the SQL baseline method. Is there any special required for SQL baselines
when parallelizing DML operations?
Thank,
Amir
