Update tables oracle

Question

I'm trying to update a row of a oracle table, the value of update is in an other table. In the first table I have two rows who can determine in the second table the value.

I try to make these two sentences:

merge into  participacion pn
using(
select valor_documento,id_tipo_documento,id_participante from 
participantes
) par
on (
pn.valor_documento=par.valor_documento
and pn.id_tipo_documento=par.id_tipo_documento
)
WHEN MATCHED THEN UPDATE
SET       pn.id_participante     = par.id_participante

or:

Update participacion pa set id_participante=(select id_participante
from participante where valor_documento=pa.valor_documento and id_tipo_documento=pa.id_tipo_documento)

In both cases the update cost a lot of time because I have 500000 rows in a table and more than 3500000 rows in the other one.

Do you have another idea about how to make this update?

I think this post will be helpful: http://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table — Hambone, Oct 23 '15 at 13:08
I Dont know until noe this concept, thank you, I will try it — Carlos Nicolas, Oct 23 '15 at 14:11

score 0 · Answer 1 · answered Oct 23 '15 at 13:39

Your MERGE statement could be rewritten as:

merge into participacion pn
using participantes par
  on (pn.valor_documento = par.valor_documento
      and pn.id_tipo_documento = par.id_tipo_documento)
when matched then
update set pn.id_participante = par.id_participante;

(i.e. since your source query is selecting everything from the participantes table, and you're not doing any calculations, you don't need to use a subquery, you can just directly reference the table).

Both forms of the MERGE are the most straightforward, efficient way of doing the update, IMHO. So, with that in mind, perhaps you need to look at indexes - how many rows are you expecing the join between the two tables to produce? Maybe an index on (valor_documento, id_tipo_documento) from the participacion table would help speed things up a bit, depending on how many rows are expected to be affected by the merge.

One table has 500000 rows and the another one has 3500000 rows. Next mondays I try it and I'll send you the best option for this case — Carlos Nicolas, Oct 23 '15 at 14:14

score 0 · Answer 2 · answered Oct 26 '15 at 06:57

0

Finally I've created an index like this

CREATE INDEX PK_temporal on participacion (valor_documento,id_tipo_documento)

And then I've updated the table , The update was made in only 2 minutes, After the update i've removed the index.

answered Oct 26 '15 at 06:57

Carlos Nicolas

1

Update tables oracle

2 Answers2