Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

Help with BULK COLLECT Update Statement

I'm trying to write a update statement that will update one column in a new table I have made. I am using some pseudo code like below to try and do this, but it is painfully slow. I'm completely new to Oracle, but I have read something about BULK COLLECT and FORALL that is supposed to speed things up. I haven't found an example anywhere trying to do exactly what I'm trying below. Can anyone help me or point me in the right direction? Thanks

UPDATE DB1.NEW_TABLE
SET DB1.NEW_TABLE.REFERENCE_ID =
(SELECT a.REFERENCE_ID
FROM DB2.ORIGINAL_TABLE a
WHERE DB1.NEW_TABLE.ID_NBR = a.actual_value)
0
sonofstimpy1
Asked:
sonofstimpy1
1 Solution
 
HuaMinChenBusiness AnalystCommented:
Try to add index on column actual_value.
0
 
sonofstimpy1Author Commented:
Unfortunately I don't own the table, so I cannot add an index.
0
 
slightwv (䄆 Netminder) Commented:
A loop using a collection and bulk collect will not be faster than native DML.

How many rows do both tables have?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
your update is using a correlated subquery right, so why don't you check it is running slow instead of trying to change it to the bulk update etc because if you are going to change it then you have write some code to procedurally iterate through the main table NEW_TABLE
to pick up the required records, then take the value from the another table ORIGINAL_TABLE for that particular records and then transfer the contents to the pl/sql arrays i,e associative arrays/collections accordingly and then run a bulk update to the required updates to the table NEW_TABLE.

It may also not be helpful in terms of performance in doing all the above and hence probably trying to find what is causing your update statement to run slowly and then doing a fix there should do the job here for this kind of update sql performance issue.

Thanks,
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now