| MERGE statement tuning [message #233445] |
Thu, 26 April 2007 03:00  |
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 #233620 is a reply to message #233521] |
Thu, 26 April 2007 23:14  |
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 #233666 is a reply to message #233644] |
Fri, 27 April 2007 02:33 |
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 #233882 is a reply to message #233787] |
Fri, 27 April 2007 19:51 |
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 |
michael_bialik
Messages: 621
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
