Oracle Update query - Cursor

Question

I want to update FirstTable from SecondTable, but there are many complications. This is the main query:

UPDATE x.FirstTable Table1
set Table1.UpdatedColumn = (
  SELECT Table2.V_PROD_CODE
  FROM y.SecondTable Table2
  WHERE Table2.XX_V_ACCOUNT_ID1 = Table1.XX_V_ACCOUNT_ID1
  AND Table2.XX_V_ACCOUNT_ID2 = Table1.XX_V_ACCOUNT_ID2
  AND Table2.XX_V_ACCOUNT_ID3 = Table1.XX_V_ACCOUNT_ID3
  AND  Table2.c_Date between '17-Apr-2018' and '27-Apr-2018'
  AND Table2.c_DATE = Table1.c_date)
WHERE Table1.c_date between '17-Apr-2018' and '27-Apr-2018'
AND length(Table1.xx_v_account_id1) = 12;

It is taking too long, so I thought about creating a cursor:

create or replace procedure wco as
  cursor UpdateCursor is 
    SELECT Table2.V_PROD_CODE
    FROM y.SecondTable Table2
    INNER JOIN x.FirstTable Table1 on SUBSTR(Table1.V_CAST_REF_CODE, 6, 8) = SUBSTR(Table2.V_CAST_REF_CODE, 6, 8)
    WHERE Table2.XX_PRODUCT_CODECCOUNT_ID1 = Table1.XX_PRODUCT_CODECCOUNT_ID1
    AND Table2.XX_PRODUCT_CODECCOUNT_ID2 = Table1.XX_PRODUCT_CODECCOUNT_ID2
    AND Table2.XX_PRODUCT_CODECCOUNT_ID3 = Table1.XX_PRODUCT_CODECCOUNT_ID3
    AND  Table2.fic_mis_date between '17-Apr-2018' and '27-Apr-2018'
    AND Table2.c_DATE = Table1.c_date
for update;
    v_PRODUCT_CODE Table2.V_PROD_CODE%type;

begin
  open UpdateCursor;
  loop
    fetch UpdateCursor into v_PRODUCT_CODE;
    exit when UpdateCursor%notfound;

    update XXBADWH.xxba_dwh_instrument_master INST
    set INST.v_product_code = v_PRODUCT_CODE 
    WHERE current of UpdateCursor
    AND INST.fic_mis_date between '17-Apr-2018' and '27-Apr-2018'
    AND length (INST.xx_v_account_id1) = 12;
  end loop;

  close UpdateCursor;
end;


exec wco;

drop procedure wco;

What is wrong with the query? and any better practices?

Why would you expect a row-by-row update being faster than one single update statement? — Wernfried Domscheit
– Wernfried Domscheit, Commented Jun 28, 2018 at 11:41
What gives you the idea that there's something wrong with the query? Error messages? Incorrect results? ??? — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jun 28, 2018 at 11:49
The first query is taking too long, the second query has errors, but apparently the second query does not solve our main concern, performance, since I am using a cursor — Samer_Azar
– Samer_Azar, Commented Jun 28, 2018 at 11:55

Gordon Linoff · Accepted Answer · 2018-06-28 11:44:10Z

For this query:

UPDATE x.FirstTable Table1
    SET Table1.UpdatedColumn = (
            SELECT Table2.V_PROD_CODE
           FROM y.SecondTable Table2
           WHERE Table2.XX_V_ACCOUNT_ID1 = Table1.XX_V_ACCOUNT_ID1 AND
                 Table2.XX_V_ACCOUNT_ID2 = Table1.XX_V_ACCOUNT_ID2 AND
                 Table2.XX_V_ACCOUNT_ID3 = Table1.XX_V_ACCOUNT_ID3 AND
                 Table2.c_Date between DATE '2018-04-17'  and DATE '2018-04-27' AND
                 Table2.c_DATE = Table1.c_date
          )
WHERE Table1.c_date between DATE '2018-04-17' and DATE '2018-04-27' AND
      length(Table1.xx_v_account_id1) = 12;

You want indexes on Table1(length(xx_v_account_id1), c_date) and Table2(XX_V_ACCOUNT_ID1, XX_V_ACCOUNT_ID2, XX_V_ACCOUNT_ID3, c_DATE).

I would start with the indexes (tested on a select select statement). Cursors are rarely the route to better performance.

Collectives™ on Stack Overflow

Oracle Update query - Cursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related