Skip to Main Content
  • Questions

  • Direct Path for the INSERTS using MERGE

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vasu.

Asked: October 24, 2002 - 2:05 pm UTC

Last updated: July 16, 2013 - 12:22 pm UTC

Version: 9.1.2

Viewed 10K+ times! This question is

You Asked

Hello Tom,

You site has been very much helpful.

We are using Oracle 9i feature MERGE as part of our incremental load approach.
My question is,
Would it be possible to make MERGE statement to use "direct path" for INSERTS ?.

Will the hints like /*+ APPEND */ works ?.


Thanks in advance.

Vasu

and Tom said...

Nope -- think about it.

insert can append cause it adds new -- above the HWM. to rollback -- just ignore the newly loaded data (we don't have to UNDO the writes above the HWM)

Merge -- writes into the MIDDLE of the table. Hence -- it cannot skip UNDO, must generate UNDO in the event the statement is to be UNDONE.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Vasu, October 24, 2002 - 4:41 pm UTC

So there is no way to make INSERTs cuased by MERGE to write above HWM ?

Thanks,
Vasu

Tom Kyte
October 24, 2002 - 5:12 pm UTC

nope

MERGE with APPEND?

VA, January 24, 2005 - 1:33 pm UTC

See

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1951476814728#12441672161348 <code>

Does that mean that a MERGE with the PARALLEL hint (and parallel dml enabled) does indeed do the INSERT part above the HWM?

Thanks

Tom Kyte
January 24, 2005 - 3:34 pm UTC 

<b>Lets try it out -- turns out the insert component could write above the high water mark, but the update will always and forever be an update

I am in NOarchive log mode for this test -- so the absence of redo indicates it did an append:</b>


ops$tkyte@ORA9IR2> create table t1
  2  as
  3  select object_id, rpad('*',80,'*') data
  4    from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t1 parallel 2;
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2
  2  as
  3  select * from t1 where 1=0;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t2 parallel 2;
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> ALTER SESSION ENABLE PARALLEL DML;
 
Session altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> column value new_val V
ops$tkyte@ORA9IR2> select * from v$sysstat where name  = 'redo size';
 
STATISTIC# NAME                                CLASS      VALUE
---------- ------------------------------ ---------- ----------
       115 redo size                               2 2498449472
 
ops$tkyte@ORA9IR2> insert /*+ append */ into t2 select * from t1;
 
27949 rows created.
 
ops$tkyte@ORA9IR2> select value, value-&v diff from v$sysstat where name = 'redo size';
old   1: select value, value-&v diff from v$sysstat where name = 'redo size'
new   1: select value, value-2498449472 diff from v$sysstat where name = 'redo size'
 
     VALUE       DIFF
---------- ----------
2498548752      99280
 
<b>just a baseline, an insert append of these rows generated about 99k of redo</b>


ops$tkyte@ORA9IR2> rollback;
 
Rollback complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from v$sysstat where name  = 'redo size';
 
STATISTIC# NAME                                CLASS      VALUE
---------- ------------------------------ ---------- ----------
       115 redo size                               2 2498561448
 
ops$tkyte@ORA9IR2> merge /*+ append parallel( t2 ) */ into t2
  2  using t1
  3  on ( t1.object_id = t2.object_id )
  4  when matched then update set data = substr(t1.data,1,79)
  5  when not matched then insert values ( t1.object_id, t1.data );
 
27949 rows merged.
 
ops$tkyte@ORA9IR2> select value, value-&v diff from v$sysstat where name = 'redo size';
old   1: select value, value-&v diff from v$sysstat where name = 'redo size'
new   1: select value, value-2498561448 diff from v$sysstat where name = 'redo size'
 
     VALUE       DIFF
---------- ----------
2498680404     118956

<b>and the merge -- about 118k -- given that a conventional path operation:</b>

 
ops$tkyte@ORA9IR2> rollback;
 
Rollback complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from v$sysstat where name  = 'redo size';
 
STATISTIC# NAME                                CLASS      VALUE
---------- ------------------------------ ---------- ----------
       115 redo size                               2 2498688004
 
ops$tkyte@ORA9IR2> merge /*+ noappend noparallel( t2 ) */ into t2
  2  using t1
  3  on ( t1.object_id = t2.object_id )
  4  when matched then update set data = substr(t1.data,1,79)
  5  when not matched then insert values ( t1.object_id, t1.data );
 
27949 rows merged.
 
ops$tkyte@ORA9IR2> select value, value-&v diff from v$sysstat where name = 'redo size';
old   1: select value, value-&v diff from v$sysstat where name = 'redo size'
new   1: select value, value-2498688004 diff from v$sysstat where name = 'redo size'
 
     VALUE       DIFF
---------- ----------
2501595832    2907828

<b>generated 2.9meg, it is safe to say "the merge inserted in append mode" above</b>
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
<b>but now we have committed.... so:</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from v$sysstat where name  = 'redo size';
 
STATISTIC# NAME                                CLASS      VALUE
---------- ------------------------------ ---------- ----------
       115 redo size                               2 2501595916
 
ops$tkyte@ORA9IR2> merge /*+ append parallel( t2 ) */ into t2
  2  using t1
  3  on ( t1.object_id = t2.object_id )
  4  when matched then update set data = substr(t1.data,1,79)
  5  when not matched then insert values ( t1.object_id, t1.data );
 
27949 rows merged.
 
ops$tkyte@ORA9IR2> select value, value-&v diff from v$sysstat where name = 'redo size';
old   1: select value, value-&v diff from v$sysstat where name = 'redo size'
new   1: select value, value-2501595916 diff from v$sysstat where name = 'redo size'
 
     VALUE       DIFF
---------- ----------
2512736924   11141008
 
<b>that merge was in fact an UPDATE and the redo reflects that</b>

ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from v$sysstat where name  = 'redo size';
 
STATISTIC# NAME                                CLASS      VALUE
---------- ------------------------------ ---------- ----------
       115 redo size                               2 2512737680
 
ops$tkyte@ORA9IR2> merge /*+ noappend noparallel( t2 ) */ into t2
  2  using t1
  3  on ( t1.object_id = t2.object_id )
  4  when matched then update set data = substr(t1.data,1,79)
  5  when not matched then insert values ( t1.object_id, t1.data );
 
27949 rows merged.
 
ops$tkyte@ORA9IR2> select value, value-&v diff from v$sysstat where name = 'redo size';
old   1: select value, value-&v diff from v$sysstat where name = 'redo size'
new   1: select value, value-2512737680 diff from v$sysstat where name = 'redo size'
 
     VALUE       DIFF
---------- ----------
2523941844   11204164
 
ops$tkyte@ORA9IR2> set autotrace off



<b>actually, hmmm, i just tested without parallel dml and sure enough, the APPEND hint does in fact modify the behaviour of the MERGE, interesting -- every time I answer something with a test case to back it up, I goof.

proof further that statements without supporting evidence are just -- well, statements

thanks for making me look just a tad deeper at this.</b>

Additional information

Denis Avdonin, September 13, 2006 - 11:06 am UTC

A bit of additional information:-

The above is true for heap organised tables, however does not work for index-organised tables. I have run the example with t2 being an IOT and it generates the same amount of redo with both /*+ append */ and /*+ noappend */ when merging. Insert also generates lots of redo comparing to the amount generated when t2 is a heap organised table. I.e. direct path (at least through the /*+ append */ hint) does not work for IOTs.

Also if we keep t2 as heap organised, but add a primary key on object_id, i.e. adding an index on the column, the amount of redo increases significantly. I guess this is due to the fact that we cannot use direct path to write into the index segment, so redo accounts for it's maintenance.

Tom Kyte
September 13, 2006 - 3:03 pm UTC

append skips only for things that can write above the high water mark (REAL TABLES, heap tables)

index organized tables are indexes....

append only skips it for the TABLE segment.

pradipdas, January 30, 2007 - 1:44 am UTC

As developer we are very helpfull from TOM

Strange Behaviour of ORA-12838

Snehasish Das, July 02, 2013 - 2:59 pm UTC

Hi Tom,

Good day. Hope you are fine and doing well.

As per my knowledge parallel write is similar to APPEND i.e Direct path write. And after direct path write if we want to select we should get the ora-12838 error.

Can you please explain me the below strange behavior of ORA-12838.
SNEHASISH @ sdat >insert /*+ PARALLEL(EMP,4) */ into empl EMP
  2  select 2,owner from all_objects where rownum < 30;

29 rows created.

Elapsed: 00:00:00.26
SNEHASISH @ sdat >select * from empl;

     EMPID|ENAME
----------|----------------------------------------------------------------
       123|Tuna
       231|Abhimaniu
       342|Chandru
       435|Bibhu
         1|SYS
         1|SYS
         1|PUBLIC
         1|SYS


SNEHASISH @ sdat >insert /*+ PARALLEL(EMP,4) */ into empl EMP
  2  select * from empl;

46 rows created.

Elapsed: 00:00:00.01
SNEHASISH @ sdat >select * from empl;
select * from empl
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.01
SNEHASISH @ sdat >


Why is it not giving error in the first scenario.

Thanks and Regards,
Snehasish Das
Tom Kyte
July 02, 2013 - 5:24 pm UTC

you shall have to provide 100% of the steps to reproduce, from start to finish

ops$tkyte%ORA11GR2> create table t as select 2 id, owner from all_objects where 1=0;

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert /*+ PARALLEL(EMP,4) */ into t EMP
  2  select 2,owner from all_objects where rownum < 3;

2 rows created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t;

        ID OWNER
---------- ------------------------------
         2 SYS
         2 SYS

2 rows selected.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert /*+ PARALLEL(EMP,4) */ into t EMP
  2  select * from t;

2 rows created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t;

        ID OWNER
---------- ------------------------------
         2 SYS
         2 SYS
         2 SYS
         2 SYS

4 rows selected.



have you read about enabling parallel DML?

http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel003.htm#CACFJJGG 




ops$tkyte%ORA11GR2> create table t as select 2 id, owner from all_objects where 1=0;

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> alter session enable parallel dml;

Session altered.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert /*+ PARALLEL(EMP,4) */ into t EMP
  2  select 2,owner from all_objects where rownum < 3;

2 rows created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t;
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Strange Behaviour of ORA-12838

Snehasish Das, July 02, 2013 - 3:14 pm UTC

Hi Tom,

There is a commit/ Rollback after the first select.

The database version is
Oracle Database 11g Enterprise Edition 11.2.0.3.0 64bit Production

System:- IBM/AIX RISC System/6000

Regards,
Snehasish Das.
Tom Kyte
July 02, 2013 - 5:25 pm UTC

no there wasn't, not in what you posted.

use *exact* paste and cuts just like I do

include *everything* from start to finish, just like I do

Strange Behaviour of ORA-12838

A reader, July 03, 2013 - 7:10 am UTC

Hi Tom,

Apologies for not giving the complete test scenario.

Please find the complete test scenario.

SNEHASISH @ sdat >alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.01
SNEHASISH @ sdat >create table empl(empno number,ename varchar2(200));

Table created.

Elapsed: 00:00:00.16
SNEHASISH @ sdat >insert into empl values (212,'Subodh');

1 row created.

Elapsed: 00:00:00.20
SNEHASISH @ sdat >insert into empl values (215,'Jasjeet');

1 row created.

Elapsed: 00:00:00.00
SNEHASISH @ sdat >insert into empl values (219,'Venkat');

1 row created.

Elapsed: 00:00:00.06
SNEHASISH @ sdat >insert into empl values (219,'Pierre');

1 row created.

Elapsed: 00:00:00.00
SNEHASISH @ sdat >commit;

Commit complete.

Elapsed: 00:00:00.00
SNEHASISH @ sdat >insert /*+ PARALLEL(EMP,4) */ into EMPL EMP
  2  select * from emp;
select * from emp
              *
ERROR at line 2:
ORA-00942: table or view does not exist 


Elapsed: 00:00:00.01
SNEHASISH @ sdat >insert /*+ PARALLEL(EMP,4) */ into EMPL EMP
  2  select * from empl;

4 rows created.

Elapsed: 00:00:02.45
SNEHASISH @ sdat >select * from empl;
select * from empl
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel 


Elapsed: 00:00:00.02
SNEHASISH @ sdat >rollback;

Rollback complete.

Elapsed: 00:00:00.01
SNEHASISH @ sdat >insert /*+ PARALLEL(EMP,4) */ into EMPL EMP
  2  select object_id,object_name from all_objects where rownum < 10;

9 rows created.

Elapsed: 00:00:00.53
SNEHASISH @ sdat >select * from empl;

     EMPNO|ENAME                                                                                                                                                                                                                                                                                            
----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                         
       212|Subodh                                                                                                                                                                                                                                                                                           
       215|Jasjeet                                                                                                                                                                                                                                                                                          
       219|Venkat                                                                                                                                                                                                                                                                                           
       219|Pierre                                                                                                                                                                                                                                                                                           
       100|ORA$BASE                                                                                                                                                                                                                                                                                         
       116|DUAL                                                                                                                                                                                                                                                                                             
       117|DUAL                                                                                                                                                                                                                                                                                             
       279|MAP_OBJECT                                                                                                                                                                                                                                                                                       
       280|MAP_OBJECT                                                                                                                                                                                                                                                                                       
       365|SYSTEM_PRIVILEGE_MAP                                                                                                                                                                                                                                                                             
       367|SYSTEM_PRIVILEGE_MAP                                                                                                                                                                                                                                                                             
       368|TABLE_PRIVILEGE_MAP                                                                                                                                                                                                                                                                              
       370|TABLE_PRIVILEGE_MAP                                                                                                                                                                                                                                                                              

13 rows selected.

Elapsed: 00:00:00.04
SNEHASISH @ sdat >rollback;

Rollback complete.

Elapsed: 00:00:00.00
SNEHASISH @ sdat >spool off;


Regards,
Snehasish Das
Tom Kyte
July 16, 2013 - 12:22 pm UTC 

ops$tkyte%ORA11GR2> alter session enable parallel dml;

Session altered.

ops$tkyte%ORA11GR2> create table empl(empno number,ename varchar2(200));

Table created.

ops$tkyte%ORA11GR2> insert into empl values (212,'Subodh');

1 row created.

ops$tkyte%ORA11GR2> insert into empl values (215,'Jasjeet');

1 row created.

ops$tkyte%ORA11GR2> insert into empl values (219,'Venkat');

1 row created.

ops$tkyte%ORA11GR2> insert into empl values (219,'Pierre');

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> insert /*+ PARALLEL(EMP,4) */ into EMPL EMP
  2  select * from empl;

4 rows created.

ops$tkyte%ORA11GR2> select * from empl;
select * from empl
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> insert /*+ PARALLEL(EMP,4) */ into EMPL EMP
  2  select object_id,object_name from all_objects where rownum < 10;

9 rows created.

ops$tkyte%ORA11GR2> select * from empl;
select * from empl
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


maybe the query did not go parallel on your system, use dbms_xplan.display_cursor to display the plan that was used.

Re: Strange Behaviour of ORA-12838

David, July 16, 2013 - 1:55 am UTC

When I look at the plan for the insert /*+ PARALLEL(EMP,4) */ into EMPL EMP select object_id,object_name from all_objects where rownum < 10; it starts
INSERT STATEMENT
LOAD TABLE CONVENTIONAL
so for some reason, it does not chose to do a parallel process when reading ALL_OBJECTS. Probably because of the nature of the all_objects view.

If I insert select * from EMPL where rownum < 10 it uses parallel.

So the answer to Snehasish's "Why is it not giving error [ORA-12838] in the first scenario." is because it is not actually doing a parallel insert in the all_objects scenario.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions