UPDATE con JOIN en ORACLE

Submitted by hminguet on 8 July, 2008 - 09:16.

Supongamos que queremos actualizar en nuestra bbdd ORACLE el campo de costes de la tabla de hechos FAC_TABLE con el coste unitario de nuestra tabla de COSTES.

Podemos hacerlo de dos maneras:

1. (Lenta, pero si es para pocos datos o para lanzarlo esporádicamente nos puede valer)

update FAC_TABLE ft
set COSTE_UNITARIO = (select distinct COSTE_UNITARIO  from COSTES ct 
 where (ft.id_articulo = ct.id_articulo);

2. (la mejor manera es esta, y el rendimimento es óptimo si tiene constraints)

UPDATE (
 SELECT 
   ft.COSTE_UNITARIO AS old_coste,
   ct.COSTE_UNITARIO AS new_coste
 FROM FAC_TABLE ft
 INNER JOIN COSTES ct ON ft.id_articulo = ct.id_articulo)
)
SET old_coste = new_coste;

Para que esta segunda oción funcione necesitamos tener UNIQUE or PRIMARY KEY constraint en ct.id_articulo.
Si no tienes esta constraint, puedes utilizar el hint /*+BYPASS_UJVC*/ después de la palabra UPDATE
(bypass update join view constraint). El rendimiento aumenta si tenemos la constraint pero aún sin ella debe correr mucho más que la primera opción.

Espero que os ayude.

Héctor Minguet.

‹ Recopilación scripts y consultas útiles de Oracle Entrar en SQLPlus como dba sin introducir password ›

Pruebas del UPDATE con JOIN

Submitted by carlos on 17 September, 2008 - 21:51.

He tenido la oportunidad de probar este tipo de update con tablas grandes, de varios millones de registros, y realmente funciona como comentas.
He lanzado un update con la primera opción y he decidido cancelarlo cuando he visto que comenzaba a afectar negativamente al rendimiento de la base de datos. El tiempo estimado que me daba la consola de Enterprise Manager para terminar era de 1 hora y media.

Después de cancelar he probado la segunda opción y me he encontrado con el error ORA-01779 porque no tenía una clave única definida sobre el campo de la tabla con la que hacía la join. Como la tabla era demasiado grande para crear un índice único sin estudiarlo primero, he probado la opción de incluir el hint /*+BYPASS_UJVC*/ (para hacer esto hay que asegurarse antes de que la correspondencia es realmente de 1:1, si no podemos obtener resultados inesperados), y el update se ha realizado correctamente en menos de 15 minutos, una diferencia considerable.

Ahora a ver si alguien se anima y nos cuenta la mejora que se obtiene con la segunda opción, pero creando una clave única en la tabla 'enlazada', y sin utilizar el hint.

Find with Google engine

Google