Database DataWarehousing (MOSC)

MOSC Banner

One design issue about merge and equivalent DDL

edited Apr 28, 2011 7:21PM in Database DataWarehousing (MOSC) 6 commentsAnswered ✓
 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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center