UPDATE con JOIN en ORACLE SQL

UPDATE con JOIN en ORACLE SQL
Oracle Database
hminguet 8 July, 2008 - 10:16

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

Con Oracle SQL podemos hacerlo de dos maneras:

  • Consulta 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); 

 

  • 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 opció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.

 

Submitted by Carlos on 17 September, 2008 - 22: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.

Submitted by Maricela (not verified) on 25 January, 2009 - 01:37

Hola se agradece este bypass.

Justamente tenía que actualizar unas tablas que tenian correlativos repetidos y tenian que ser secuenciales, realicé una tabla temporal y despues aplicar un update. De igual forma tenia que hacer un procedimiento almacenado pero en fin... con este bypass, todo bien. 

El tiempo de respuesta en la actualizacion el descuebe GRACIAS!!! 

gracias.

atte.

Maricela de CHILE

Submitted by Luis H (not verified) on 27 February, 2009 - 15:46

Gracias.

Lo utilice para una actualizacion de varios campos y se comporto de maravilla.

 

Submitted by bardellica (not verified) on 11 June, 2010 - 23:03

Utilice un script con este hint /*+BYPASS_UJVC*/ y funciona una muy bien... Bajo un script de 9 hs a 6 segungos....
Pero cuando lo quiero utilizar dentro de un Package aparece un error "PL/SQL: ORA-01031: Insufficient privileges"
¿ALGUIEN PUEDE AYUDARME CON ESTE TEMA?

DESDE YA MUCHAS GRACIAS.

Submitted by Marco (not verified) on 10 November, 2010 - 20:34

Bueno lo mas probable es que sea esto, mira una cosa son los permisos que tienes tu o tu usario y otra los permisos que tiene el paquete por ejemplo digamos que tu tienes el usuario au0001 y cuando corres esto si puedes hacerlo debido a tus permisos, pero cuando pones esto dentro del paquete que esta en el esquema au0002 y dicho esquema no tiene esos priviliegios, pues es claro que te truena aun si tu como usuario au0001 si tengas el permiso y seas tu el que corre el paquete , el paquete truena por ser el o su esquema mejor dicho el que no tiene los permisos.

Submitted by isunza on 28 December, 2011 - 00:16

Hola intente hacerlo de las dos maneras, el update tradicional y con el hint, con el primero se tarda bastante, lo cancele, con el segundo me marca el error ORA-01031 - Insufficient privileges, lo estoy haciendo en el mismo esquema, la tabla de la que quiero actualizar tiene llave unique en el campo que uso para unir con la tabla que tiene la información ¿A que se debe que con uno no me marque el error y con el segundo si?

Submitted by Alejandro Corona (not verified) on 8 October, 2013 - 19:53

Muchisimas gracias por tu aporte, estoy iniciando con Oracle y fue muy útil tu ayuda!!!! que tengas un excelente día!

Submitted by Fran (not verified) on 10 October, 2013 - 11:07

Me ha servido su update con join y el hint, ¡muchas gracias por su aporte!

Submitted by Diego (not verified) on 26 February, 2015 - 16:59

La explicación es muy buena y me ha servido en varios proyectos cuando la base de datos es Oracle 10g, sin embargo en Oracle 11g el hint ha desaparecido y no se puede utilizar, he leído algo acerca del tema y en todos lados lo que dice es que debo cambiar la instrucción y no utilizar más el hint bypass_ujvc.

¿Como han hecho para mitigar la eliminación del hint? ¿Es correcto mejor hacerlo por merge?

Gracias!

Submitted by Carlos on 2 March, 2015 - 23:52

Pues sí, si a partir de la versión 11g R2 el HINT BYPASS_UJVC ya no es válido porque Oracle lo ha dejado como deprecated hay que dejar de utilizarlo, y además hay que tenerlo en cuenta en upgrades o migraciones desde versiones anteriores, ya que si se utilizaba este HINT en versiones anteriores a la 11g, al lanzar las queries que contengan el hint BYPASS_UJVC el analizador devolverá un error y la query fallará.

Una buena alternativa, tal como ya apuntas, es utilizar un MERGE para este tipo de operaciones en que se haya recurrido al HINT al no disponer de una clave primaria para el campo por el que se hace la join de la SELECT del UPDATE, aunque si se puede conseguir la clave primaria o única para ese campo, seguramente el UPDATE será más rápido.

Al utilizar el MERGE, hay que asegurarse igualmente de que la JOIN entre tabla origen y destino sea INNER, que no existan duplicados en las tablas para esos registros, porque entonces podemos encontrarnos el error ORA-30926, o efectos inesperados en los resultados. Si se diera el caso, habría que anular los duplicados eliminándolos antes de hacer la join, o modificando la query añadiendo un group by para aplicar un SUM, MAX, MIN u otra operación sobre el campo con valores duplicados.

 

Libros de Administración Oracle (DBA) y PL/SQL

¿Quieres profundizar más en PL/SQL de Oracle o en administración de bases de datos Oracle? Puedes hacerlo consultando alguno de estos libros de Oracle.

Submitted by Diego (not verified) on 6 May, 2015 - 14:30

Hola!

Necesito algo de ayuda con un Update de toda una columna..

La idea es actualizar la columna reintegro de la tabla tarjeta, la cual está relacionada con otras tablas (empresa, certificado,persona,institucion) donde ley=S de la tabla institucion

Les comento las consultas que he realizado sin éxito:

==================
update tarjeta set reintegro ='0'
where (select reintegro, ley_anterior from tarjeta , institucion where ley='S')
==================
==================
Update tarjeta SET reintegro= '0'
INNER JOIN empresa on tar_emp_codigo = empcodigo
INNER JOIN certificado on empcodigo = cem_empcodigo
INNER JOIN persona on per_documento = cem_perdocumento
INNER JOIN institucion on per_inscodigo = inscodigo
WHERE (ley='S')
==================

Me podrían ayudar?

Gracias

Submitted by Hassan (not verified) on 1 June, 2015 - 22:28

Hola buenas tardes, no se si este sea el tema correcto pero tengo una problema muy extraño, tengo una tabla la cual si le hago un select con el esquema propietario de devuelve los datos correctamente pero al tratar de hacer un insert o un update con el mismo propietario me dice que tabla o vista no existe. no se si me pueden ayudar a descubrir el problema. tengo oracle 11gR2.

Submitted by Anonimo (not verified) on 7 July, 2015 - 18:31

Hola, me ha sido muy útil este foro, acá encontré la solución a mi problema. Gracias! SALUDOS!! ;)