One design issue about merge and equivalent DDL
Hi All,
My DB is 11.1. RAC 4 nodes.
Today I encountered one SQL that did merge and ran very slowly. After tuning, it
cost more than 2 hrs to finish.
According to Oracle manual and my own understanding,
1.MERGE itself can't have inner-partition parallelism. So if the target table is
non-partitioned, PML doesn't work here.
2.MERGE genertes redo and can't be suppressed.
So I think whether an equivalent CTAS works better, considering about daily
increments are relatively large amount comparing to the target table.
Pseudo SQL looks like:
create table new_table
parallel 4 nologging
as
select case
when target_table_join_columns is null
My DB is 11.1. RAC 4 nodes.
Today I encountered one SQL that did merge and ran very slowly. After tuning, it
cost more than 2 hrs to finish.
According to Oracle manual and my own understanding,
1.MERGE itself can't have inner-partition parallelism. So if the target table is
non-partitioned, PML doesn't work here.
2.MERGE genertes redo and can't be suppressed.
So I think whether an equivalent CTAS works better, considering about daily
increments are relatively large amount comparing to the target table.
Pseudo SQL looks like:
create table new_table
parallel 4 nologging
as
select case
when target_table_join_columns is null
0