Update com subselect

--Este script faz acesso full e demora muito a ser executado
UPDATE COR_MERCADORIA C
       SET C.DFLT_OM_CODIGO = (SELECT  DISTINCT D.OM_CODIGO
                           FROM COR_IDF D
                          WHERE D.DOF_SEQUENCE||D.CODIGO_DO_SITE IN (
                                                             SELECT A.DOF_SEQUENCE||A.CODIGO_DO_SITE
                                                               FROM COR_DOF A
                                                              WHERE A.DT_FATO_GERADOR_IMPOSTO BETWEEN DATAINICIAL AND DATAFINAL)
                            AND D.OM_CODIGO IN (1,2,0)
                            AND C.MERC_CODIGO = D.MERC_CODIGO)
        WHERE C.MERC_CODIGO =
        (SELECT  DISTINCT D.MERC_CODIGO
                           FROM COR_IDF D
                          WHERE D.DOF_SEQUENCE||D.CODIGO_DO_SITE IN (
                                                             SELECT A..DOF_SEQUENCE||A.CODIGO_DO_SITE
                                                               FROM COR_DOF A
                                                              WHERE A.DT_FATO_GERADOR_IMPOSTO BETWEEN DATAINICIAL AND DATAFINAL)
                            AND D.OM_CODIGO IN (1,2,0)
                            AND D.MERC_CODIGO = C.MERC_CODIGO);

--Este script faz a mesma coisa do que o de cima, porém sem fazer acesso full, assim fica muito mais rápido
DECLARE
CURSOR IDF_OM IS
SELECT DISTINCT D.OM_CODIGO, D.MERC_CODIGO
       FROM COR_IDF D, COR_DOF A
       WHERE D.DOF_SEQUENCE||D.CODIGO_DO_SITE = A.DOF_SEQUENCE||A.CODIGO_DO_SITE
         AND A.DT_FATO_GERADOR_IMPOSTO BETWEEN DATAINICIAL AND DATAFINAL
         AND D.MERC_CODIGO IS NOT NULL;
IDF_OM_DADOS  IDF_OM%rowtype;                           
BEGIN
     Open IDF_OM;
     LOOP
          fetch IDF_OM into IDF_OM_DADOS;
          EXIT WHEN IDF_OM%NOTFOUND;
         
          UPDATE COR_MERCADORIA SET DFLT_OM_CODIGO = IDF_OM_DADOS.OM_CODIGO WHERE MERC_CODIGO = IDF_OM_DADOS.MERC_CODIGO;
         -- DBMS_OUTPUT.PUT_LINE ('GRAVADO : ' || IDF_OM_DADOS.OM_CODIGO||' - '|| IDF_OM_DADOS.MERC_CODIGO);         
         
     END LOOP;
     COMMIT;
     CLOSE IDF_OM;
END;