| performance tuning [message #521870] |
Fri, 02 September 2011 13:00  |
zodiacsom
Messages: 37
Registered: December 2009
Location: pune
|
Member |
|
|
Hi All,
When i try to run merge query from sql*plus .. my system get hangout takes more then 4hr ... so i have to kill
query :
MERGE INTO LOSS L USING
(SELECT NEW_ID,transaction_type from temp_payments) T
WHEN MATCHED THEN UPDATE SET L.LOSS_TRANSCATION=T.TRANSACTION_TYPE;
records in
loss : 1955887
temp_payments : 1684013
i use /*+ append on */ ... no improvement
Please help me out ..
|
|
|
|
|
|
| Re: performance tuning [message #521872 is a reply to message #521870] |
Fri, 02 September 2011 13:15  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
It sounds strange. This statement should fail very quickly as it misses ON clause.
There should be primary key/unique constraint/index on LOSS.ID (or however is this matching column named). Is it? Or any other constraint/index on the LOSS table?
Anyway, it will take some time to go through 1.5million rows.
[Edit: After looking at that properly, index on TEMP_PAYMENTS.NEW_ID should help much more. As its content has to be unique, there should be at least unique constraint on this column anyway.]
[Updated on: Fri, 02 September 2011 23:40] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
