Performance tuning of PL/SQL Procedure [message #494302] |
Sun, 13 February 2011 01:13 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/258c07c3f5694bf37478613a3d1e3948?s=64&d=mm&r=g) |
Santrupta
Messages: 6 Registered: February 2011 Location: Johannesburg
|
Junior Member |
|
|
Hi,
I want to update two fields, owner_accnt_loc and owner_accnt_name of the table eim_asset from the output of this query. (Please note, 2.7 Million records are present in this table and ALL need to be updated)
select b.par_accnt_name,b.par_accnt_loc from eim_account b,eim_asset a
where a.ast_bill_accntname = b.name and
b.accnt_type_cd = 'Billing'
AND b.par_accnt_name IS NOT NULL
AND b.par_accnt_loc IS NOT NULL
Updating the table using normal DML commands was getting cumbersome, so I tried this PL/SQL procedure. (Attached to the message).
However, this code is also taking a huge time. (3 minutes per record!!)
I am quite lost now, please could you show me a more performance friendly way to do this?
Regards,
Santrupta
|
|
|
|
|
|
|
|
Re: Performance tuning of PL/SQL Procedure [message #494316 is a reply to message #494313] |
Sun, 13 February 2011 03:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
WHERE clause is missing this is why you have NULL.
update eim_asset a
set owner_accnt_name = ( select b.par_accnt_name
from eim_account b
where a.ast_bill_accntname = b.name
and b.accnt_type_cd = 'Billing'
AND b.par_accnt_name IS NOT NULL )
where exists ( select null
from eim_account b
where a.ast_bill_accntname = b.name
and b.accnt_type_cd = 'Billing'
AND b.par_accnt_name IS NOT NULL )
MERGE might be a better choice in this case.
Regards
Michel
[Updated on: Sun, 13 February 2011 03:26] Report message to a moderator
|
|
|
|
|
|
Re: Performance tuning of PL/SQL Procedure [message #494950 is a reply to message #494949] |
Thu, 17 February 2011 09:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Is it a column? In which table?
When you say it doesn't work, what exactly do you mean? Are you getting an error? The wrong results?
Remember - we know nothing about your tables and data except what you tell us. We can not help you further without more information.
|
|
|