Home / Questions / 344439
Accepted
Ivxn
Asked: 2020-04-09 07:44:04 +0800 CST 2020-04-09 07:44:04 +0800 CST

Optimize update in oracle

  • 772

How could I further optimize this update query, since I have to update 200,000 records and the oracle session ends and I cannot update anything, not even by placing ROWNUM

I get the error: ORA-00028: your session has been killed

UPDATE TMP_IOP_AJUSTE_TRF_RCBD_PSTVS P
SET P.PLAZA = (SELECT T.NOMCAS FROM TMP_IOP_AJUSTE_TRF_RCBD2_TEST T WHERE T.SEQ=P.SEQ)
WHERE P.NUM_DEC IN ('E422') AND P.PLAZA IS NULL AND  ROWNUM <=10000

enter image description here

oracle

2 Answers

  1. You seem to be missing indices, at least:

    CREATE INDEX indx_rcbd_pstvs_nd ON tmp_iop_ajuste_trf_rcbd_pstvs(num_dec);
CREATE INDEX indx_rcbd2t_seq ON tmp_iop_ajuste_trf_rcbd2_test(seq);

    Here the detail is that for each record in Pthe subquery is performed; Subject to knowing how is the relationship seqbetween Pand Tyou can reduce the execution time with a MERGE:

    MERGE INTO tmp_iop_ajuste_trf_rcbd_pstvs p
  USING tmp_iop_ajuste_trf_rcbd2_test t
    ON t.seq = p.seq
  WHEN MATCHED THEN UPDATE
    SET p.plaza = t.nomcas
    WHERE p.num_dec in ('E422')
      AND p.plaza is null
    • 3
  2. Best Answer
    MERGE INTO TMP_IOP_AJUSTE_TRF_RCBD_PSTVS  P
USING (
SELECT   T.NOMCAS,
         T.SEQ, 
        T.DEC_EXT
            FROM TMP_IOP_AJUSTE_TRF_RCBD2_TEST T
            WHERE T.DEC_EXT IN ('E422') AND T.NOMBRE_ARCHIVO IN ('24032020.xlsx')
      ) T
       ON (T.SEQ = P.SEQ AND P.NUM_DEC = T.DEC_EXT )
       WHEN MATCHED THEN UPDATE SET P.PLAZA= T.NOMCAS
       WHERE P.NUM_DEC IN ('E422') AND P.PLAZA IS NULL AND P.NOMBRE_ARCHIVO IN ('24032020.xlsx')
    • 1