Forum Stats

  • 3,768,996 Users
  • 2,252,894 Discussions
  • 7,874,831 Comments

Discussions

Query needs to be re-written

curious_mind
curious_mind Member Posts: 254 Bronze Badge

Hi All, I have a requirement to update certain number of records in a table on the basis of the incorrect record from the other table. below written code is taking so much of time.

Please help to enhance the performance of the below query:

set timing on;

set serveroutput on;

DECLARE

CURSOR c1 IS SELECT dstr_alt_id,eods_dstr_id FROM SEODS01.DSTR_ALT_ID DST 

WHERE length(dstr_alt_id)=27;

v_cmit_nbr           NUMBER(8):=50000;

TYPE ARRAY IS TABLE OF c1%ROWTYPE;

v_stg_array      ARRAY;

tot_cnt number;

BEGIN

---------27 length dstr_alt_id Surrogate Record Updation started--------------------------------

DBMS_OUTPUT.PUT_LINE('Updation of records where length of dstr_alt_id is 27 in DMU CT18 ACCT DSTR table started:');

OPEN c1;

              LOOP

                FETCH C1 BULK COLLECT INTO V_STG_ARRAY LIMIT v_cmit_nbr;

                DBMS_OUTPUT.PUT_LINE('Updating ' ||V_STG_ARRAY.count|| ' records');

                FORALL i IN 1..v_stg_array.COUNT

       UPDATE SEODS01.DMU_CT18_ACCT_DSTR a

SET EODS_DSTR_ID=(select eods_dstr_id from seods01.dstr_alt_id b where b.dstr_alt_id=a.dstr_alt_id)  

        WHERE EODS_DSTR_ID=v_stg_array(i).EODS_DSTR_ID and a.prtt_cde='DMU_0';

                --COMMIT;

                EXIT WHEN c1%NOTFOUND;

              END LOOP;

              CLOSE C1;

DBMS_OUTPUT.PUT_LINE('Updation of Surrogate records where length of dstr_alt_id is 27 in DMU CT18 ACCT DSTR table started completed: ');

END;

/

---------27 length dstr_alt_id Surrogate Record Updation completed------------------------------

Tagged:
«1

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,914 Red Diamond
    UPDATE SEODS01.DMU_CT18_ACCT_DSTR A
       SET A.EODS_DSTR_ID = (
                             SELECT  B.EODS_DSTR_ID
                               FROM  SEODS01.DSTR_ALT_ID B
                               WHERE B.DSTR_ALT_ID = A.DSTR_ALT_ID
                            )  
       WHERE EODS_DSTR_ID IN (
                              SELECT  DST.EODS_DSTR_ID
                                FROM  SEODS01.DSTR_ALT_ID DST 
                                WHERE LENGTH(DST.DSTR_ALT_ID) = 27
                             )
         AND A.PRTT_CDE = 'DMU_0'
    /
    

    SY.

  • curious_mind
    curious_mind Member Posts: 254 Bronze Badge

    Thanks for the reply Solomon, but the other thing is i have to update somewhere around 8 million number of records so the simple update statement i guess should not work.

    please suggest accordingly.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,914 Red Diamond
    edited Apr 9, 2021 12:31PM

    Why not? It will certainly be faster than bulk collect + forall.

    SY.

  • curious_mind
    curious_mind Member Posts: 254 Bronze Badge

    that's true but it will commit in the last only, so the redo logs will be full if i don't commit simultaneously.

    please suggest.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,914 Red Diamond

    Not redo logs but UNDO. Same as your code since commit is commented out. But UNDO that can't handle 8 million rows is most likely undersized.

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,914 Red Diamond
    edited Apr 9, 2021 6:31PM

    Anyway, post execution plan. And did you research what part takes time? How big is SEODS01.DSTR_ALT_ID table? Most likely it has small (relatively to table row count) number of rows where length = 27. Then (unless there is FBI) selecting rows where length = 27 does full scan. Try creating FBI on

    CASE LENGTH(DST.DSTR_ALT_ID)
      WHEN 27 THEN EODS_DSTR_ID
    END
    

    This will create index that containes nothing but EODS_DSTR_ID where LENGTH(DST.DSTR_ALT_ID) = 27. Then try (check execution plan):

    UPDATE SEODS01.DMU_CT18_ACCT_DSTR A
       SET A.EODS_DSTR_ID = (
                             SELECT  B.EODS_DSTR_ID
                               FROM  SEODS01.DSTR_ALT_ID B
                               WHERE B.DSTR_ALT_ID = A.DSTR_ALT_ID
                            )  
       WHERE EODS_DSTR_ID IN (
                              SELECT  DST.EODS_DSTR_ID
                                FROM  SEODS01.DSTR_ALT_ID DST 
                                WHERE CASE LENGTH(DST.DSTR_ALT_ID)
                                        WHEN 27 THEN EODS_DSTR_ID
                                      END IS NOT NULL
                             )
         AND A.PRTT_CDE = 'DMU_0'
    /
    

    You might also consider creating index on A.PRTT_CDE,A. EODS_DSTR_ID.

    SY.

  • cormaco
    cormaco Member Posts: 1,722 Bronze Crown

    You can also use merge like this (untested)

    merge into SEODS01.DMU_CT18_ACCT_DSTR A
    using (
        select  DST.EODS_DSTR_ID
        from  SEODS01.DSTR_ALT_ID DST
        where length(DST.DSTR_ALT_ID) = 27     
    ) on (DST.DSTR_ALT_ID = A.DSTR_ALT_ID and A.PRTT_CDE = 'DMU_0')
    when matched then update
    set A.EODS_DSTR_ID = DST.EODS_DSTR_ID
    
    jflack
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,914 Red Diamond

    This isn't equivalent to original. Compare:

    on (DST.DSTR_ALT_ID = A.DSTR_ALT_ID and A.PRTT_CDE = 'DMU_0')
    

    to original:

    WHERE EODS_DSTR_ID=v_stg_array(i).EODS_DSTR_ID and a.prtt_cde='DMU_0';
    

    SY.

  • cormaco
    cormaco Member Posts: 1,722 Bronze Crown

    Ah, yes I confused the column names. Like I said untested.

  • curious_mind
    curious_mind Member Posts: 254 Bronze Badge

    Please help me to understand what is wrong with this query:

    begin

     for c in (SELECT eods_dstr_id from seods01.dstr_alt_id where length(dstr_alt_id)=27)

     loop

     update SEODS01.DMU_CT18_ACCT_DSTR A

     set  A.EODS_DSTR_ID  = (select eods_dstr_id from seods01.dstr_alt_id b where b.dstr_alt_id=a.dstr_alt_id),updt_tstp=sysdate

     WHERE A.EODS_DSTR_ID = c.eods_dstr_id AND A.PRTT_CDE = 'DMU_0' ;

     commit;

     end loop;  

     DBMS_OUTPUT.PUT_LINE('Number of records Updated are: ' || SQL%ROWCOUNT);

     end;

     /