Home » RDBMS Server » Performance Tuning » MERGE statement tuning
MERGE statement tuning [message #233445] Thu, 26 April 2007 03:00 Go to next message
rmkandan
Messages: 6
Registered: April 2007
Junior Member
Hi All

I have MERGE statment to peform update or insert operation.

I popluate about 1 million records using sqlldr to a staging table.

I have to compare the record exists in main table then update with values in staging table, or else insert the record in main table.

I am using Oracle Database 10g Enterprise Edition Release 10.1.0.2.0.

The problem is the execution of this statement takes more than 3 hours to complete. The volume of main table in 6 million records.

Please help how I can tune this query or process to make it work faster.

The query is as below

  merge into  t1
  using (
	select /*+ INDEX(st1 c_PK) */ 
		* from st1 
	where (c1) 
	in (
	  select /*+ INDEX(st1 c_PK) */ 
	  max(c1) from st1 where c1 >= pStat and mo_id < pEnd 
	  group by c2
	)) t2
  on (t1.c1 = t2.c1 and t1.c2=t2.c2)
  when matched then 
	update set 
	c4 = t2.c4, 
        c5 = t2.c5,
-- all other columns here 
  when not matched then 
	insert ( c1, c2, c3, c4 ... )
	values 
	(t2.c1, t2.c2, t2.c3, t2.c4 ..);
Re: MERGE statement tuning [message #233521 is a reply to message #233445] Thu, 26 April 2007 08:06 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post EXPLAIN.
Re: MERGE statement tuning [message #233620 is a reply to message #233521] Thu, 26 April 2007 23:14 Go to previous messageGo to next message
rmkandan
Messages: 6
Registered: April 2007
Junior Member
Hi

Below is the explain plan of the Merge query

Operation Object
--------------------------------------------- ------------------------------
MERGE STATEMENT ()
MERGE () T1
VIEW ()
SEQUENCE () S1
HASH JOIN (SEMI)
NESTED LOOPS (OUTER)
TABLE ACCESS (BY INDEX ROWID) MO_T_USER_INFO_STAGING
INDEX (FULL SCAN) MO_C_UIN_MO_ID_STG_PK
TABLE ACCESS (BY INDEX ROWID) T1
INDEX (UNIQUE SCAN) C_T1_1
VIEW () VW_NSO_2

Re: MERGE statement tuning [message #233644 is a reply to message #233620] Fri, 27 April 2007 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An explain plan without formatting is unreadable.

Regards
Michel
Re: MERGE statement tuning [message #233666 is a reply to message #233644] Fri, 27 April 2007 02:33 Go to previous messageGo to next message
rmkandan
Messages: 6
Registered: April 2007
Junior Member
Operation                                     Object
--------------------------------------------- ------------------------------
MERGE STATEMENT ()
 MERGE ()                                     T2
  VIEW ()
   SEQUENCE ()                                S1
    HASH JOIN (SEMI)
     NESTED LOOPS (OUTER)
      TABLE ACCESS (BY INDEX ROWID)           T1
       INDEX (FULL SCAN)                      T1_C_PK
      TABLE ACCESS (BY INDEX ROWID)           T2
       INDEX (UNIQUE SCAN)                    T1_C_1
     VIEW ()                                  VW_NSO_2
 
Operation                                     Object
--------------------------------------------- ------------------------------
      SORT (GROUP BY)
       TABLE ACCESS (BY INDEX ROWID)          T1
        INDEX (RANGE SCAN)                    T1_PK

[Updated on: Fri, 27 April 2007 02:34]

Report message to a moderator

Re: MERGE statement tuning [message #233787 is a reply to message #233445] Fri, 27 April 2007 08:22 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. What columns are included in indexes: T1_C_PK, T1_C_1, T1_PK?

2. Why index T1_C_1 belong to table T2? Is it correct?

3. Do you have any other indexes? On what columns?

Re: MERGE statement tuning [message #233882 is a reply to message #233787] Fri, 27 April 2007 19:51 Go to previous messageGo to next message
rmkandan
Messages: 6
Registered: April 2007
Junior Member
Hi


merge into  t1
  using (
	select /*+ INDEX(st1 c_PK) */ 
		* from st1 
	where (c1) 
	in (
	  select /*+ INDEX(st1 c_PK) */ 
	  max(c3) from st1 where c3 >= pstart and c3 < pEnd 
	  group by c2
	)) t2
  on (t1.c1 = t2.c1 and t1.c2=t2.c2)
  when matched then 
	update set 
	c4 = t2.c4, 
        c5 = t2.c5,
-- all other columns here 
  when not matched then 
	insert ( c1, c2, c3, c4 ... )
	values 
	(t2.c1, t2.c2, t2.c3, t2.c4 ..);


Operation                                     Object
--------------------------------------------- ------------------------------
MERGE STATEMENT ()
 MERGE ()                                     T1
  VIEW ()
   SEQUENCE ()                                S1
    HASH JOIN (SEMI)
     NESTED LOOPS (OUTER)
      TABLE ACCESS (BY INDEX ROWID)           ST1
       INDEX (FULL SCAN)                      ST1_C1_PK
      TABLE ACCESS (BY INDEX ROWID)           T1
       INDEX (UNIQUE SCAN)                    T1_C_1
     VIEW ()                                  VW_NSO_2

Operation                                     Object
--------------------------------------------- ------------------------------
      SORT (GROUP BY)
       TABLE ACCESS (BY INDEX ROWID)          ST1
        INDEX (RANGE SCAN)                    ST1_C1_PK


ST1_C1_PK is C3 as Primary Key

Other Indexes are in C1 and C2 as combained Index

Please take the above query and help me, to resolve and improve the speed of execution.

The query takes 6 secs for merging 1000 records.

I need to process about 14000000 records in T1.

Thank you to all.
Re: MERGE statement tuning [message #234018 is a reply to message #233445] Sun, 29 April 2007 01:46 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try:

merge into  t1
  using ( SELECT /*+ ORDERED */ st1.*
          FROM 
             ( SELECT DISTINCT MAX(C3) c3 FROM ST1
               WHERE C3 >= pstart AND C3 < pEnd
               GROUP BY C2 ) vt, st1
          WHERE ST1.C3 = VT.C3
	) t2
  on (t1.c1 = t2.c1 and t1.c2=t2.c2)
  when matched then 
	update set 
	c4 = t2.c4, 
        c5 = t2.c5,
-- all other columns here 
  when not matched then 
	insert ( c1, c2, c3, c4 ... )
	values 
	(t2.c1, t2.c2, t2.c3, t2.c4 ..);


Post EXPLAIN and timings of the new statement if it doesn't solve the problem.

HTH.
Michael
Previous Topic: Some Suggestions
Next Topic: hash partitioning.. help.
Goto Forum:
  


Current Time: Sat Dec 10 22:38:02 CST 2016

Total time taken to generate the page: 0.04278 seconds