I’m updating one table using bulk collect (FORALL). Update statement is similar to below update statement.
FORALL i in collection.first…collection.last
set col1 := ,
Col3 := ,
where mytable.X = collection(i);
Here mytable has 6 millions data.
and mytable.x column is primary key . so for every collection value oracle looks index first for value of X and then go for table scan to get other columns Col1…col10 .
If i have 100 collection so, for every collection oracle first look index and then scan table to get other columns that i need to set.
Problem is that there is lot of I/O between memory and disk …so correspondingly update query has run more than 40 minutes only for 100 collection.
Could you guys have better way to do this update statement to enhance performance.
one solution i see i can create index on all columns that i need to update, this way oracle would only see index and wouldn’t scan table. But problem here is that performance of other queries that are based on table (mytable) can be degraded.