3

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)

Ghazouani Med amine
  • 73
  • 1
  • 1
  • 9
  • 1
    Will this be done during a service window, so that you need it to run quickly at all costs? Or will the query run when there are active users? A parallel approach that updates all rows in one transaction will be very expensive, so not recommended when the database is used. In that case I would rather find the IDs in one select and copy them to a temporary table. Then I would process them in batches with a commit between each batch. That means one expensive select to get the IDs and then a number of fairly cheap updates. – ewramner Mar 03 '19 at 07:17
  • Some more details about the update might help. You only posted a `select` query. I think I understand the issue with the `in` subquery and Jon Heller's `merge` suggestion could help, or `update (select...join...) set...`, and then there is `dbms_parallel_execute` or plain `/*+ parallel */` for the parallel aspect. – William Robertson Mar 03 '19 at 12:30

3 Answers3

5

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
0

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.

sic wang
  • 3
  • 1
0

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;
psaraj12
  • 4,772
  • 2
  • 21
  • 30