I am currently starting to work with Oracle Databases and it is common to have to UPDATE from one table to another. As the databases that I have access to do not have many records, if I execute an Update with an inner join, I cannot evaluate the performance of the Update in time. But there is one Db that I understand has tables with 50,000 records or more.
So I want to be prepared so that if I have to modify the tables in question, I can execute an instruction correctly and not one that blocks the db.
The update I use regularly is like this:
UPDATE (
SELECT Tabla1.Valor AS ValorAnterior
,Tabla2.Codigo AS ValorNuevo
FROM Tabla1
INNER JOIN Tabla2 ON Tabla1.Valor = Tabla2.Descripcion
WHERE Tabla1.Tipo = 1
) t
SET t.ValorAnterior = t.ValorNuevo;
On the net I have seen the following instructions:
1) UPDATE Tabla1 SET Tabla1.Valor = (SELECT Tabla2.Codigo
FROM Tabla2
WHERE Tabla1.Valor = Tabla2.Descripcion)
WHERE Tabla1.Tipo=1
AND EXISTS (SELECT Tabla2.Codigo
FROM Tabla2
WHERE Tabla1.Valor = Tabla2.Descripcion);
2) MERGE INTO Tabla1 trg
USING (
SELECT t1.RowId AS rid
,t2.Codigo
FROM Tabla1 t1
JOIN Tabla2 t2 ON Tabla1.Valor = Tabla2.Descripcion
WHERE Tabla1.Tipo = 1
) src
ON (trg.RowId = src.rid)
WHEN MATCHED
THEN
UPDATE
SET trg.Valor = Codigo;
3) UPDATE (
SELECT t1.Valor
,t2.Codigo
FROM Tabla1 t1
INNER JOIN Tabla2 t2 ON t1.Valor = t2.Descripcion
WHERE t1.Tipo = 1
)
SET t1.Valor = t2.Codigo;
If someone can point me to the most efficient instruction, I am very grateful.
I understand that there are no performance differences between using inner and left join connectors with the old format (in fact it is better to get used to using them because of query order). In addition, 50 thousand records should not give you performance problems, Oracle is a base designed to work with tables with millions of records.
If you have a replica DB (development or testing) you can do the exercise of executing the instructions and then rollback.
With that you can have a simple estimate of performance in time, apart from the cost information that the execution plan gives you.