UPDATE con JOIN en ORACLE SQL

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.

 

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.

En respuesta a por Carlos

 

Hola Hector y Carlos, 

 

Excelente hint, lo he utilizado por años sin ningun problema.

Pero hoy me paso el sig. error:

 

Tengo una tabla PRIMARIA 

* PREPAQ_MIC

 

Y tambien tengo dos vistas.

* VW_DATACRED

* VW_CNCH_MPIO

 

Esta ultima vista esta definida asiI:

CREATE OR REPLACE FORCE VIEW FIRA.VW_CNCH_MPIO
(CDGPROG, CDGEF, CDGMU)
AS
select CDGPROG, CDGEF, trim(column_value)   CDGMU
              from (    SELECT   CDGPROG, CDGEF, APO.VALORES  CDGMU
                        FROM FIRA.APL_APOYO  APO
                        WHERE CDGPROG = 'CNCH' AND CDGMU = 'VAR'
                        ORDER BY CDGEF, CDGMU
                   ) t,
                   xmltable(('"' || replace(CDGMU, ',', '","') || '"'));

Con el sig. script UPDATE, intento modificar un campo de la tabla primaria (PRE.APOYO) con el valor de la vista anterior

UPDATE /*+BYPASS_UJVC*/
    (
        SELECT PRE.ESQUEMA, PRE.STATUS, PRE.NUMCONTROL, PRE.CLNS, PRE.CDGRG, PRE.CDGCLNS, PRE.CICLO, PRE.CDGCL, PRE.CANTENTRE, PRE.SUSTRAE, PRE.MOTIVORECHAZO, PRE.APOYO AS PRE_APOYO,
               VDC.CDGEF, VDC.CDGMU, VDC.MUNICIPIO, VDC.CDGLO, VDC.LOCALIDAD,  VDC.POB_LOC, VDC.GRADMARG, 
               VDC.ACTIVIDAD, VDC.CVESECTOR, VDC.NOM_SECTOR, VDC.CDGSUBSEC, VDC.NOM_SUBSECTOR, 
               CNCH.CDGPROG AS APOYO_CNCH
            FROM PREPAQ_MIC     PRE,
                 VW_DATACRED    VDC,
                 VW_CNCH_MPIO   CNCH  
            WHERE
                PRE.CLNS = VDC.CLNS AND PRE.CDGCLNS = VDC.CDGCLNS AND PRE.CICLO = VDC.CICLO AND PRE.CDGCL = VDC.CDGCL
                AND VDC.CDGEF = CNCH.CDGEF AND VDC.CDGMU = CNCH.CDGMU
                AND TRIM(PRE.APOYO) IS NULL
    ) SET PRE_APOYO = APOYO_CNCH

Sin embargo al ejecutar el script señala el siguiente error:

ORA-01031: Privilegios insuficientes.

La vista fue creada por el mismo usuario que lo ejecuta (FIRA) y tienen permiso de crear tablas (grant create table to FIRA)

Ojala me puedas ayudar

Saludos!

En respuesta a por arzamm@yahoo.com

No sé si la manera en que están definidas las vistas puedan influir en algo, y también es importante que te asegures de que la correspondencia de valores en la join es de 1 a 1.

Para localizar el problema yo probaría a simplificar la consulta. Para saber si tiene alguna relación con las vistas podrías crear tablas físicas a partir de los valores de una consulta de las vistas, y validar sobre estas tablas que no tengas registros duplicados antes de probar el update con las tablas.

Otra cosa que te podría afectar es el IS NULL mezclado con las joins. Puedes definir las joins en formato FROM .. INNER JOIN .. ON, como en el ejemplo, y dejar TRIM(PRE.APOYO) IS NULL sólo en la parte del WHERE.

Espero que localices pronto dónde está el problema, y que nos lo expliques.

Un saludo,

En respuesta a por Carlos

En mi caso me daba el error: 

Error SQL: ORA-01031: privilegios insuficientes 01031. 00000 -  "insufficient privileges".

La causa es que tenia dos esquemas diferentes y en uno de ellos no tenia permisos de escritura sobre una de las tablas. Al sacar los datos del esquema que no tenia permisos, para una tabla temporal en el esquema que si tenia permisos, hizo el update correctamente, ya con las dos tablas en mi mismo esquema.

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

Gracias.

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

 

En respuesta a por Luis H (no verificado)

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.

En respuesta a por bardellica (no verificado)

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.

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?

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

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!

En respuesta a por Diego (no verificado)

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.


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

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.

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