CODEWITHSUNDEEP
sqloracle-databasesql-updatesql-optimization
Ghazouani Med amine
Ghazouani Med amine

Reputation: 73

How to optimise massive updates in oracle

I need to update a huge table in oracle that contains ~40*10^6 records. The number of rows that will be modified will be approximately 10^7. The query that determines the rows that will be updated is complex and involves joins. It takes 30 mins just to identify the id of rows that will be updated.

Select p.some_id from
(select some_id, col2,col3 from t1 where col2='someVulue' and col3 ='someValue') p 
inner join (select some_id from t2 where t2.col3='someValue') q
on p.some_id=q.some_id

Now in order to do the update i need to add another join or use the IN statement which will make things even worse.

Is there a way to parallelize this ? Or to do a batch update (update 25*10^4 rows each time)? is there a way to tell oracle to only update first n rows ? then n->2n, then 2n->3n ... ?

The script will be ran on a production environment so table rebuild is not an alternative.

The update consists of setting a boolean column to true.(if this can help)

Upvotes: 3

Views: 20308

Answers (3)

psaraj12
psaraj12

Reputation: 5072

Do a batch update (update 25*10^4 rows each time)? is there a way to tell oracle to only update first n rows ? then n->2n, then 2n->3n where n is 10 here 

 for loop i in 1..10
 loop
 update table1
 set column_val=x
 where rowid in (select rowid from table1
 where rownum >= (((i-1) * (25*10^4))+1) and rownum <= i*(25*10^4)
 );
 end loop;

Upvotes: 0

sic wang
sic wang

Reputation: 3

there are several ways. 1) split your query into small chunks. for example: split the table by primary key or add a clause 2) if you need to run this quite frequently, you can consider partition table, then run update parallel 3) check if your indexes are built properly BTW, I don't think 30 mins is a long time if it does not cause a performance issue for your application or block other queries, it is quite normal.

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36817

The fastest way to perform a massive update is with parallel DML, like this:

alter session enable parallel dml;
update /*+ parallel(16) */ some_table set some_column = 1;
commit;

There are a lot of little gotchas to watch out for. You need to have Enterprise Edition. The UPDATE will get an exclusive lock on the table, so nobody else will be able to write to the table at the same time. Your system must have sufficient resources to support a large UPDATE, such as enough redo, undo, CPU, I/O, and a sanely configured system.

(You'll probably want to change the number 16 in my example to a number appropriate for your system. If you want to max out the performance, but perhaps at the expense of other processes, set the number equal to the number of cores.)

Oracle parallelism is great but it's not really optimizing. It's making the system work harder, not smarter. Before you try parallelism you might want to look at the SQL statement that's used in the UPDATE. You might also want to try using MERGE instead. The MERGE syntax is a bit trickier at first, but it can help avoid repeating joins, and allows hash joins which can run faster for changing a large percentage of rows.

Upvotes: 5

Related Questions