UPDATE with INNER JOIN in Oracle 11G (Performance)

3

I am currently starting to work with Databases in Oracle and it is common to have to perform UPDATE from one table to another. Since the databases I have access to do not have many records, if I run an Update with inner join I can not evaluate the performance of the Update in time. But there is a DB that I understand has tables with 50,000 records or more.

Then I want to be prepared so that if given the case I must modify in the mentioned tables to execute an instruction correctly and not one that blocks the db.

The update that 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;

Through the network 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 tell me, the most efficient instruction, very grateful.

    
asked by raintrooper 12.04.2018 в 23:25
source

0 answers