/+ APPEND_VALUES / or /+ APPEND/ , which one is efficient one for insert operation in oracle

Question

I have written one procedure, which will insert data from master table to working table . Its kind of data replication from one table to another table. So, at a time more than 2-5 Million data will be inserted in working table. Now I am facing performance issue here.

I can't use forall statement when DML operation, as many if-else condition is there before inserting.

So, I used hints like /*+ APPEND_VALUES / and /+ APPEND*/. But am not sure which one will be appropriate for this.

PROCEDURE process_assets 


  CURSOR c_get_unprocessed_rcd (p_last_run_date    DATE,
                                p_cur_run_date     DATE)
  IS
       SELECT ROWID,
              covered_line_id,
              op_type,
              seq_id,
              NRT_CREATION_DATE,
              NRT_last_update_DATE,
              contract_id,
              service_line_id,
              gg_create_date,
              instance_id,
              item_type_flag,
              nvl(old_item_type_flag,item_type_flag) old_item_type_flag
         FROM XXCCS_DS_CVDLIB_DTLEB_ES
        WHERE TRGT_CREATE_DATE BETWEEN p_last_run_date AND p_cur_run_date
     ORDER BY seq_id ASC;

  TYPE l_get_unprocessed_type IS TABLE OF c_get_unprocessed_rcd%ROWTYPE
     INDEX BY PLS_INTEGER;

  l_get_unprocessed_rcd   l_get_unprocessed_type;

BEGIN

  OPEN c_get_unprocessed_rcd (p_last_run_date, p_cur_run_date);

  LOOP
     EXIT WHEN c_get_unprocessed_rcd%NOTFOUND;

     FETCH c_get_unprocessed_rcd
        BULK COLLECT INTO l_get_unprocessed_rcd
        LIMIT 10000;


     FOR i IN l_get_unprocessed_rcd.FIRST .. l_get_unprocessed_rcd.LAST
     LOOP
        BEGIN
           if
            l_get_unprocessed_rcd (i).op_type = 'I'
           THEN
              BEGIN 

                 INSERT
                   INTO XXCCS_UEE_SRVC_ES_ACT_KEY (covered_line_id,
                                                   instance_id,
                                                   seq_id,
                                                   OP_TYPE,
                                                   SOT_TABLE,
                                                   SOT_CREATE_DATE,
                                                   SOT_LAST_UPDATE_DATE,
                                                   GG_CREATE_DATE,
                                                   contract_id,
                                                   item_type_flag)
                 VALUES (l_get_unprocessed_rcd (i).covered_line_id,
                         l_get_unprocessed_rcd (i).instance_id,
                         l_get_unprocessed_rcd (i).seq_id,
                         l_get_unprocessed_rcd (i).op_type,
                         L_SOT_TABLE_NAME,
                         l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
                         l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
                         l_get_unprocessed_rcd (i).GG_CREATE_DATE,
                         l_get_unprocessed_rcd (i).contract_id,
                         l_get_unprocessed_rcd (i).item_type_flag);



                 INSERT
                   INTO XXCCS_UEE_DEVC_ES_CVD_KEY_V3 (instance_id,
                                                   OP_TYPE,
                                                   SOT_TABLE,
                                                   SOT_CREATE_DATE,
                                                   SOT_LAST_UPDATE_DATE,
                                                   GG_CREATE_DATE,
                                                   seq_id,
                                                   item_type_flag)
                 VALUES (l_get_unprocessed_rcd (i).instance_id,
                         l_get_unprocessed_rcd (i).op_type,
                         L_SOT_TABLE_NAME,
                         l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
                         l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
                         l_get_unprocessed_rcd (i).GG_CREATE_DATE,
                         l_get_unprocessed_rcd (i).seq_id,
                         l_get_unprocessed_rcd (i).item_type_flag);


              END;

           ELSIF l_get_unprocessed_rcd (i).op_type = 'U'
           THEN
              BEGIN 
                 INSERT
                   INTO XXCCS_UEE_SRVC_ES_ACT_KEY (covered_line_id,
                                                   instance_id,
                                                   seq_id,
                                                   OP_TYPE,
                                                   SOT_TABLE,
                                                   SOT_CREATE_DATE,
                                                   SOT_LAST_UPDATE_DATE,
                                                   GG_CREATE_DATE,
                                                   contract_id,
                                                   item_type_flag)
                 VALUES (l_get_unprocessed_rcd (i).covered_line_id,
                         l_get_unprocessed_rcd (i).instance_id,
                         l_get_unprocessed_rcd (i).seq_id,
                         l_get_unprocessed_rcd (i).op_type,
                         L_SOT_TABLE_NAME,
                         l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
                         l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
                         l_get_unprocessed_rcd (i).GG_CREATE_DATE,
                         l_get_unprocessed_rcd (i).contract_id,
                         l_get_unprocessed_rcd (i).item_type_flag);



                 INSERT
                   INTO XXCCS_UEE_DEVC_ES_CVD_KEY_V3 (instance_id,
                                                   OP_TYPE,
                                                   SOT_TABLE,
                                                   SOT_CREATE_DATE,
                                                   SOT_LAST_UPDATE_DATE,
                                                   GG_CREATE_DATE,
                                                   seq_id,
                                                   item_type_flag)
                 VALUES (l_get_unprocessed_rcd (i).instance_id,
                         l_get_unprocessed_rcd (i).op_type,
                         L_SOT_TABLE_NAME,
                         l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
                         l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
                         l_get_unprocessed_rcd (i).GG_CREATE_DATE,
                         l_get_unprocessed_rcd (i).seq_id,
                         l_get_unprocessed_rcd (i).item_type_flag);



                 IF (l_get_unprocessed_rcd (i).item_type_flag in('P','S') AND  l_get_unprocessed_rcd (i).old_item_type_flag IN('C'))
                    OR
                    (l_get_unprocessed_rcd (i).item_type_flag in('C') AND  l_get_unprocessed_rcd (i).old_item_type_flag IN('P','S')) 
                 THEN
                    BEGIN
                         INSERT
                           INTO XXCCS_UEE_SRVC_ES_ACT_KEY (covered_line_id,
                                                           instance_id,
                                                           seq_id,
                                                           OP_TYPE,
                                                           SOT_TABLE,
                                                           SOT_CREATE_DATE,
                                                           SOT_LAST_UPDATE_DATE,
                                                           GG_CREATE_DATE,
                                                           contract_id,
                                                           item_type_flag)
                         VALUES (l_get_unprocessed_rcd (i).covered_line_id,
                                 l_get_unprocessed_rcd (i).instance_id,
                                 l_get_unprocessed_rcd (i).seq_id,
                                 'D',
                                 L_SOT_TABLE_NAME,
                                 l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
                                 l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
                                 l_get_unprocessed_rcd (i).GG_CREATE_DATE,
                                 l_get_unprocessed_rcd (i).contract_id,
                                 l_get_unprocessed_rcd (i).old_item_type_flag);



                         INSERT
                           INTO XXCCS_UEE_DEVC_ES_CVD_KEY_V3 (instance_id,
                                                           OP_TYPE,
                                                           SOT_TABLE,
                                                           SOT_CREATE_DATE,
                                                           SOT_LAST_UPDATE_DATE,
                                                           GG_CREATE_DATE,
                                                           seq_id,
                                                           item_type_flag)
                         VALUES (l_get_unprocessed_rcd (i).instance_id,
                                 'D',
                                 L_SOT_TABLE_NAME,
                                 l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
                                 l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
                                 l_get_unprocessed_rcd (i).GG_CREATE_DATE,
                                 l_get_unprocessed_rcd (i).seq_id,
                                 l_get_unprocessed_rcd (i).old_item_type_flag);




                    END;
                END IF;
              END;       

           ELSE
              BEGIN
                 INSERT
                   INTO XXCCS_UEE_SRVC_ES_ACT_KEY (covered_line_id,
                                                   instance_id,
                                                   seq_id,
                                                   OP_TYPE,
                                                   SOT_TABLE,
                                                   SOT_CREATE_DATE,
                                                   SOT_LAST_UPDATE_DATE,
                                                   GG_CREATE_DATE,
                                                   contract_id,
                                                   item_type_flag)
                 VALUES (l_get_unprocessed_rcd (i).covered_line_id,
                         l_get_unprocessed_rcd (i).instance_id,
                         l_get_unprocessed_rcd (i).seq_id,
                         l_get_unprocessed_rcd (i).op_type,
                         L_SOT_TABLE_NAME,
                         l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
                         l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
                         l_get_unprocessed_rcd (i).GG_CREATE_DATE,
                         l_get_unprocessed_rcd (i).contract_id,
                         l_get_unprocessed_rcd (i).item_type_flag);

BEGIN SELECT COUNT (1) INTO l_cvd_exists FROM XXCCS_DS_CVDLINEIB_DETAILEB WHERE instance_id = l_get_unprocessed_rcd (i).instance_id;

                    IF l_cvd_exists = 0
                    THEN
                       INSERT
                         INTO XXCCS_UEE_DEVC_ES_CVD_KEY_V3 (
                                 instance_id,
                                 OP_TYPE,
                                 SOT_TABLE,
                                 SOT_CREATE_DATE,
                                 SOT_LAST_UPDATE_DATE,
                                 GG_CREATE_DATE,
                                 seq_id,
                                 item_type_flag)
                          VALUES (
                                    l_get_unprocessed_rcd (i).instance_id,
                                    l_get_unprocessed_rcd (i).op_type,
                                    L_SOT_TABLE_NAME,
                                    l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
                                    l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
                                    l_get_unprocessed_rcd (i).GG_CREATE_DATE,
                                    l_get_unprocessed_rcd (i).seq_id,
                                    l_get_unprocessed_rcd (i).item_type_flag);
                    ELSE
                       INSERT
                         INTO XXCCS_UEE_DEVC_ES_CVD_KEY_V3 (
                                 instance_id,
                                 OP_TYPE,
                                 SOT_TABLE,
                                 SOT_CREATE_DATE,
                                 SOT_LAST_UPDATE_DATE,
                                 GG_CREATE_DATE,
                                 seq_id,
                                 item_type_flag)
                          VALUES (
                                    l_get_unprocessed_rcd (i).instance_id,
                                    'U',
                                    L_SOT_TABLE_NAME,
                                    l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
                                    l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
                                    l_get_unprocessed_rcd (i).GG_CREATE_DATE,
                                    l_get_unprocessed_rcd (i).seq_id,
                                    l_get_unprocessed_rcd (i).item_type_flag);
                    END IF;
                    end;
                         end;


                         ------- Like this if else block condition will continue

here replication is happening from XXCCS_DS_CVDLIB_DTLEB_ES to XXCCS_UEE_SRVC_ES_ACT_KEY, XXCCS_UEE_DEVC_ES_CVD_KEY these two tables

So, i need to be executed this in minimal time. Can anyone suggest what hints I can use with insert query, or we can go for any other method to execute

"*i need to be executed this in minimal time*" - then don't use a cursor. Use `insert ... select` with the appropriate `WHERE` conditions for the source data. — , Jul 01 '19 at 10:46

ibre5041 · Answer 1 · 2019-07-01T11:57:40.497

First of all, they both serve different purposes.

APPEND is used only when you have INSERT from SELECT: "INSERT INTO ...(SELECT * FROM ...)"
APPEND_VALUES was introduced lately for: "INSERT INTO ... VALUES(...). This hint can also be used for bulk operations.

Both hint have various constraints and can be ignored in various situations. See Why Append hint is ignored

IHMO if you do care about performance of insert statement try to focus first on

triggers overhead
constraint overhead
index maintenance overhead

Also check execution plan of the INSERT statements while is your procedure running. If you see:

LOAD TABLE CONVENTIONAL - then APPEND hint is ignored for some reason
LOAD TABLE AS SELECT - Direct path load is used and APPEND hint is in plance

Florin Ghita · Answer 2 · 2019-07-01T11:28:59.090

0

If you insert with direct path(that happens when you use append or append_values) row by row please be aware that you will create a new extent with every insert statement issued(or use an existing free extent if available. If not, it will create a new one). So it is important to test the speed but test also the size of the target table.

A test of the speed of APPEND VS APPEND_VALUES you can find here(in a nutshell, append_values is faster than append)

Some condensed info about append you can find also on this oracle base article

edited Jul 01 '19 at 11:28

answered Jul 01 '19 at 11:04

Florin Ghita

17,525
6
57
76

mind the points here about nologging and archivelog mode ... if you get faster inserts but the conditions are just right (or wrong) you'll want to do a full system backup afterwards or you won't be covered in certain recovery situations – thatjeffsmith Jul 01 '19 at 13:49

Popeye · Accepted Answer · 2019-07-03T05:57:01.697

I must say you can use FORALL.

I have tried to convert your IF-ELSE logic into FORALL.

You just need to add WHERE condition in FORALL according to logic.

Look at the following code:

PROCEDURE PROCESS_ASSETS AS

    CURSOR C_GET_UNPROCESSED_RCD (
        P_LAST_RUN_DATE DATE,
        P_CUR_RUN_DATE DATE
    ) IS
    SELECT
        ROWID,
        COVERED_LINE_ID,
        OP_TYPE,
        SEQ_ID,
        NRT_CREATION_DATE,
        NRT_LAST_UPDATE_DATE,
        CONTRACT_ID,
        SERVICE_LINE_ID,
        GG_CREATE_DATE,
        INSTANCE_ID,
        ITEM_TYPE_FLAG,
        NVL(OLD_ITEM_TYPE_FLAG, ITEM_TYPE_FLAG) OLD_ITEM_TYPE_FLAG
    FROM
        XXCCS_DS_CVDLIB_DTLEB_ES;
              --   ORDER BY seq_id ASC; -- not needed

    TYPE L_GET_UNPROCESSED_TYPE IS
        TABLE OF C_GET_UNPROCESSED_RCD%ROWTYPE INDEX BY PLS_INTEGER;
    L_GET_UNPROCESSED_RCD   L_GET_UNPROCESSED_TYPE;

BEGIN 

OPEN C_GET_UNPROCESSED_RCD; LOOP
    EXIT WHEN C_GET_UNPROCESSED_RCD%NOTFOUND;

    FETCH C_GET_UNPROCESSED_RCD BULK COLLECT INTO L_GET_UNPROCESSED_RCD LIMIT 10000;

    FORALL I IN 1..L_GET_UNPROCESSED_RCD.COUNT
        INSERT INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
            COVERED_LINE_ID,
            INSTANCE_ID,
            SEQ_ID,
            OP_TYPE,
            SOT_TABLE,
            SOT_CREATE_DATE,
            SOT_LAST_UPDATE_DATE,
            GG_CREATE_DATE,
            CONTRACT_ID,
            ITEM_TYPE_FLAG
        )
            SELECT
                L_GET_UNPROCESSED_RCD(I).COVERED_LINE_ID,
                L_GET_UNPROCESSED_RCD(I).INSTANCE_ID,
                L_GET_UNPROCESSED_RCD(I).SEQ_ID,
                L_GET_UNPROCESSED_RCD(I).OP_TYPE,
                L_SOT_TABLE_NAME,
                L_GET_UNPROCESSED_RCD(I).NRT_CREATION_DATE,
                L_GET_UNPROCESSED_RCD(I).NRT_LAST_UPDATE_DATE,
                L_GET_UNPROCESSED_RCD(I).GG_CREATE_DATE,
                L_GET_UNPROCESSED_RCD(I).CONTRACT_ID,
                L_GET_UNPROCESSED_RCD(I).ITEM_TYPE_FLAG
            FROM
                DUAL
            WHERE
                L_GET_UNPROCESSED_RCD(I).OP_TYPE = 'I'; -- added this condition from your IF-ELSE

--
--

    FORALL I IN 1..L_GET_UNPROCESSED_RCD.COUNT
        INSERT INTO XXCCS_UEE_DEVC_ES_CVD_KEY (
            INSTANCE_ID,
            OP_TYPE,
            SOT_TABLE,
            SOT_CREATE_DATE,
            SOT_LAST_UPDATE_DATE,
            GG_CREATE_DATE,
            SEQ_ID,
            ITEM_TYPE_FLAG
        )
            SELECT
                L_GET_UNPROCESSED_RCD(I).INSTANCE_ID,
                L_GET_UNPROCESSED_RCD(I).OP_TYPE,
                L_SOT_TABLE_NAME,
                L_GET_UNPROCESSED_RCD(I).NRT_CREATION_DATE,
                L_GET_UNPROCESSED_RCD(I).NRT_LAST_UPDATE_DATE,
                L_GET_UNPROCESSED_RCD(I).GG_CREATE_DATE,
                L_GET_UNPROCESSED_RCD(I).SEQ_ID,
                L_GET_UNPROCESSED_RCD(I).ITEM_TYPE_FLAG
            FROM
                DUAL
            WHERE
                L_GET_UNPROCESSED_RCD(I).OP_TYPE = 'I'; -- added this condition from your IF-ELSE
--
--

    FORALL I IN 1..L_GET_UNPROCESSED_RCD.COUNT
        INSERT INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
            COVERED_LINE_ID,
            INSTANCE_ID,
            SEQ_ID,
            OP_TYPE,
            SOT_TABLE,
            SOT_CREATE_DATE,
            SOT_LAST_UPDATE_DATE,
            GG_CREATE_DATE,
            CONTRACT_ID,
            ITEM_TYPE_FLAG
        )
            SELECT
                L_GET_UNPROCESSED_RCD(I).COVERED_LINE_ID,
                L_GET_UNPROCESSED_RCD(I).INSTANCE_ID,
                L_GET_UNPROCESSED_RCD(I).SEQ_ID,
                L_GET_UNPROCESSED_RCD(I).OP_TYPE,
                L_SOT_TABLE_NAME,
                L_GET_UNPROCESSED_RCD(I).NRT_CREATION_DATE,
                L_GET_UNPROCESSED_RCD(I).NRT_LAST_UPDATE_DATE,
                L_GET_UNPROCESSED_RCD(I).GG_CREATE_DATE,
                L_GET_UNPROCESSED_RCD(I).CONTRACT_ID,
                L_GET_UNPROCESSED_RCD(I).ITEM_TYPE_FLAG
            FROM
                DUAL
            WHERE
                L_GET_UNPROCESSED_RCD(I).OP_TYPE = 'U'; -- added this condition from your IF-ELSE
--
--
--
--

    FORALL I IN 1..L_GET_UNPROCESSED_RCD.COUNT
        INSERT INTO XXCCS_UEE_DEVC_ES_CVD_KEY (
            INSTANCE_ID,
            OP_TYPE,
            SOT_TABLE,
            SOT_CREATE_DATE,
            SOT_LAST_UPDATE_DATE,
            GG_CREATE_DATE,
            SEQ_ID,
            ITEM_TYPE_FLAG
        )
            SELECT
                L_GET_UNPROCESSED_RCD(I).INSTANCE_ID,
                L_GET_UNPROCESSED_RCD(I).OP_TYPE,
                L_SOT_TABLE_NAME,
                L_GET_UNPROCESSED_RCD(I).NRT_CREATION_DATE,
                L_GET_UNPROCESSED_RCD(I).NRT_LAST_UPDATE_DATE,
                L_GET_UNPROCESSED_RCD(I).GG_CREATE_DATE,
                L_GET_UNPROCESSED_RCD(I).SEQ_ID,
                L_GET_UNPROCESSED_RCD(I).ITEM_TYPE_FLAG
            FROM
                DUAL
            WHERE
                L_GET_UNPROCESSED_RCD(I).OP_TYPE = 'U'; -- added this condition from your IF-ELSE

--
--

    FORALL I IN 1..L_GET_UNPROCESSED_RCD.COUNT
        INSERT INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
            COVERED_LINE_ID,
            INSTANCE_ID,
            SEQ_ID,
            OP_TYPE,
            SOT_TABLE,
            SOT_CREATE_DATE,
            SOT_LAST_UPDATE_DATE,
            GG_CREATE_DATE,
            CONTRACT_ID,
            ITEM_TYPE_FLAG
        )
            SELECT
                L_GET_UNPROCESSED_RCD(I).COVERED_LINE_ID,
                L_GET_UNPROCESSED_RCD(I).INSTANCE_ID,
                L_GET_UNPROCESSED_RCD(I).SEQ_ID,
                'D',
                L_SOT_TABLE_NAME,
                L_GET_UNPROCESSED_RCD(I).NRT_CREATION_DATE,
                L_GET_UNPROCESSED_RCD(I).NRT_LAST_UPDATE_DATE,
                L_GET_UNPROCESSED_RCD(I).GG_CREATE_DATE,
                L_GET_UNPROCESSED_RCD(I).CONTRACT_ID,
                L_GET_UNPROCESSED_RCD(I).OLD_ITEM_TYPE_FLAG
            FROM
                DUAL
            WHERE -- added this condition from your IF-ELSE
                L_GET_UNPROCESSED_RCD(I).OP_TYPE = 'U'
                AND ( ( L_GET_UNPROCESSED_RCD(I).ITEM_TYPE_FLAG IN (
                    'P',
                    'S'
                )
                        AND L_GET_UNPROCESSED_RCD(I).OLD_ITEM_TYPE_FLAG IN (
                    'C'
                ) )
                      OR ( L_GET_UNPROCESSED_RCD(I).ITEM_TYPE_FLAG IN (
                    'C'
                )
                           AND L_GET_UNPROCESSED_RCD(I).OLD_ITEM_TYPE_FLAG IN (
                    'P',
                    'S'
                ) ) );

--
--

    FORALL I IN 1..L_GET_UNPROCESSED_RCD.COUNT
        INSERT INTO XXCCS_UEE_DEVC_ES_CVD_KEY (
            INSTANCE_ID,
            OP_TYPE,
            SOT_TABLE,
            SOT_CREATE_DATE,
            SOT_LAST_UPDATE_DATE,
            GG_CREATE_DATE,
            SEQ_ID,
            ITEM_TYPE_FLAG
        )
            SELECT
                L_GET_UNPROCESSED_RCD(I).INSTANCE_ID,
                'D',
                L_SOT_TABLE_NAME,
                L_GET_UNPROCESSED_RCD(I).NRT_CREATION_DATE,
                L_GET_UNPROCESSED_RCD(I).NRT_LAST_UPDATE_DATE,
                L_GET_UNPROCESSED_RCD(I).GG_CREATE_DATE,
                L_GET_UNPROCESSED_RCD(I).SEQ_ID,
                L_GET_UNPROCESSED_RCD(I).OLD_ITEM_TYPE_FLAG
            FROM
                DUAL
            WHERE -- added this condition from your IF-ELSE
                L_GET_UNPROCESSED_RCD(I).OP_TYPE = 'U'
                AND ( ( L_GET_UNPROCESSED_RCD(I).ITEM_TYPE_FLAG IN (
                    'P',
                    'S'
                )
                        AND L_GET_UNPROCESSED_RCD(I).OLD_ITEM_TYPE_FLAG IN (
                    'C'
                ) )
                      OR ( L_GET_UNPROCESSED_RCD(I).ITEM_TYPE_FLAG IN (
                    'C'
                )
                           AND L_GET_UNPROCESSED_RCD(I).OLD_ITEM_TYPE_FLAG IN (
                    'P',
                    'S'
                ) ) );

     -- YOUR OLD CODE --
     -------------------
     -------------------
     -------------------
     -------------------
     -------------------

--     FOR i IN l_get_unprocessed_rcd.FIRST .. l_get_unprocessed_rcd.LAST
--     LOOP--2
--        BEGIN--3
--           if
--            l_get_unprocessed_rcd (i).op_type = 'I'
--           THEN
--              BEGIN
--
--                 INSERT
--                   INTO XXCCS_UEE_SRVC_ES_ACT_KEY (covered_line_id,
--                                                   instance_id,
--                                                   seq_id,
--                                                   OP_TYPE,
--                                                   SOT_TABLE,
--                                                   SOT_CREATE_DATE,
--                                                   SOT_LAST_UPDATE_DATE,
--                                                   GG_CREATE_DATE,
--                                                   contract_id,
--                                                   item_type_flag)
--                 VALUES (l_get_unprocessed_rcd (i).covered_line_id,
--                         l_get_unprocessed_rcd (i).instance_id,
--                         l_get_unprocessed_rcd (i).seq_id,
--                         l_get_unprocessed_rcd (i).op_type,
--                         L_SOT_TABLE_NAME,
--                         l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
--                         l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
--                         l_get_unprocessed_rcd (i).GG_CREATE_DATE,
--                         l_get_unprocessed_rcd (i).contract_id,
--                         l_get_unprocessed_rcd (i).item_type_flag);
--
--
--
--                 INSERT
--                   INTO XXCCS_UEE_DEVC_ES_CVD_KEY (instance_id,
--                                                   OP_TYPE,
--                                                   SOT_TABLE,
--                                                   SOT_CREATE_DATE,
--                                                   SOT_LAST_UPDATE_DATE,
--                                                   GG_CREATE_DATE,
--                                                   seq_id,
--                                                   item_type_flag)
--                 VALUES (l_get_unprocessed_rcd (i).instance_id,
--                         l_get_unprocessed_rcd (i).op_type,
--                         L_SOT_TABLE_NAME,
--                         l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
--                         l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
--                         l_get_unprocessed_rcd (i).GG_CREATE_DATE,
--                         l_get_unprocessed_rcd (i).seq_id,
--                         l_get_unprocessed_rcd (i).item_type_flag);
--
--
--
--           ELSIF l_get_unprocessed_rcd (i).op_type = 'U'
--           THEN
--              BEGIN 
--                 INSERT
--                   INTO XXCCS_UEE_SRVC_ES_ACT_KEY (covered_line_id,
--                                                   instance_id,
--                                                   seq_id,
--                                                   OP_TYPE,
--                                                   SOT_TABLE,
--                                                   SOT_CREATE_DATE,
--                                                   SOT_LAST_UPDATE_DATE,
--                                                   GG_CREATE_DATE,
--                                                   contract_id,
--                                                   item_type_flag)
--                 VALUES (l_get_unprocessed_rcd (i).covered_line_id,
--                         l_get_unprocessed_rcd (i).instance_id,
--                         l_get_unprocessed_rcd (i).seq_id,
--                         l_get_unprocessed_rcd (i).op_type,
--                         L_SOT_TABLE_NAME,
--                         l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
--                         l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
--                         l_get_unprocessed_rcd (i).GG_CREATE_DATE,
--                         l_get_unprocessed_rcd (i).contract_id,
--                         l_get_unprocessed_rcd (i).item_type_flag);
--
--
--
--                 INSERT
--                   INTO XXCCS_UEE_DEVC_ES_CVD_KEY (instance_id,
--                                                   OP_TYPE,
--                                                   SOT_TABLE,
--                                                   SOT_CREATE_DATE,
--                                                   SOT_LAST_UPDATE_DATE,
--                                                   GG_CREATE_DATE,
--                                                   seq_id,
--                                                   item_type_flag)
--                 VALUES (l_get_unprocessed_rcd (i).instance_id,
--                         l_get_unprocessed_rcd (i).op_type,
--                         L_SOT_TABLE_NAME,
--                         l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
--                         l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
--                         l_get_unprocessed_rcd (i).GG_CREATE_DATE,
--                         l_get_unprocessed_rcd (i).seq_id,
--                         l_get_unprocessed_rcd (i).item_type_flag);
--
--
--
--                 IF (l_get_unprocessed_rcd (i).item_type_flag in('P','S') AND  l_get_unprocessed_rcd (i).old_item_type_flag IN('C'))
--                    OR
--                    (l_get_unprocessed_rcd (i).item_type_flag in('C') AND  l_get_unprocessed_rcd (i).old_item_type_flag IN('P','S')) 
--                 THEN
--                    BEGIN
--                         INSERT
--                           INTO XXCCS_UEE_SRVC_ES_ACT_KEY (covered_line_id,
--                                                           instance_id,
--                                                           seq_id,
--                                                           OP_TYPE,
--                                                           SOT_TABLE,
--                                                           SOT_CREATE_DATE,
--                                                           SOT_LAST_UPDATE_DATE,
--                                                           GG_CREATE_DATE,
--                                                           contract_id,
--                                                           item_type_flag)
--                         VALUES (l_get_unprocessed_rcd (i).covered_line_id,
--                                 l_get_unprocessed_rcd (i).instance_id,
--                                 l_get_unprocessed_rcd (i).seq_id,
--                                 'D',
--                                 L_SOT_TABLE_NAME,
--                                 l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
--                                 l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
--                                 l_get_unprocessed_rcd (i).GG_CREATE_DATE,
--                                 l_get_unprocessed_rcd (i).contract_id,
--                                 l_get_unprocessed_rcd (i).old_item_type_flag);
--
--
--
--                         INSERT
--                           INTO XXCCS_UEE_DEVC_ES_CVD_KEY (instance_id,
--                                                           OP_TYPE,
--                                                           SOT_TABLE,
--                                                           SOT_CREATE_DATE,
--                                                           SOT_LAST_UPDATE_DATE,
--                                                           GG_CREATE_DATE,
--                                                           seq_id,
--                                                           item_type_flag)
--                         VALUES (l_get_unprocessed_rcd (i).instance_id,
--                                 'D',
--                                 L_SOT_TABLE_NAME,
--                                 l_get_unprocessed_rcd (i).NRT_CREATION_DATE,
--                                 l_get_unprocessed_rcd (i).NRT_LAST_UPDATE_DATE,
--                                 l_get_unprocessed_rcd (i).GG_CREATE_DATE,
--                                 l_get_unprocessed_rcd (i).seq_id,
--                                 l_get_unprocessed_rcd (i).old_item_type_flag);


                         ------- Like this if else block condition will continue

--

--------- UPDATE ---------

I have totally changed my answer now. You will gonna like it. I have used INSERT ALL. Just, I am not sure what L_SOT_TABLE_NAME is. so I have considered it as a String. You can fetch in variable and use it accordingly in your procedure.

PROCEDURE PROCESS_ASSETS AS
BEGIN
INSERT
    ALL 

    -- 1

     WHEN OP_TYPE = 'I' THEN
        INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
            COVERED_LINE_ID,
            INSTANCE_ID,
            SEQ_ID,
            OP_TYPE,
            SOT_TABLE,
            SOT_CREATE_DATE,
            SOT_LAST_UPDATE_DATE,
            GG_CREATE_DATE,
            CONTRACT_ID,
            ITEM_TYPE_FLAG
        )
        VALUES (
            COVERED_LINE_ID,
            INSTANCE_ID,
            SEQ_ID,
            OP_TYPE,
            'L_SOT_TABLE_NAME',
            NRT_CREATION_DATE,
            NRT_LAST_UPDATE_DATE,
            GG_CREATE_DATE,
            CONTRACT_ID,
            ITEM_TYPE_FLAG
        )

        --2

        WHEN OP_TYPE = 'I' THEN
            INTO XXCCS_UEE_DEVC_ES_CVD_KEY (
                INSTANCE_ID,
                OP_TYPE,
                SOT_TABLE,
                SOT_CREATE_DATE,
                SOT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                SEQ_ID,
                ITEM_TYPE_FLAG
            )
            VALUES (
                INSTANCE_ID,
                OP_TYPE,
                'L_SOT_TABLE_NAME',
                NRT_CREATION_DATE,
                NRT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                SEQ_ID,
                ITEM_TYPE_FLAG
            )

        --3

        WHEN OP_TYPE = 'U' THEN
            INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
                COVERED_LINE_ID,
                INSTANCE_ID,
                SEQ_ID,
                OP_TYPE,
                SOT_TABLE,
                SOT_CREATE_DATE,
                SOT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                CONTRACT_ID,
                ITEM_TYPE_FLAG
            )
            VALUES (
                COVERED_LINE_ID,
                INSTANCE_ID,
                SEQ_ID,
                OP_TYPE,
                'L_SOT_TABLE_NAME',
                NRT_CREATION_DATE,
                NRT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                CONTRACT_ID,
                ITEM_TYPE_FLAG
            )

        -- 4

        WHEN OP_TYPE = 'U' THEN
            INTO XXCCS_UEE_DEVC_ES_CVD_KEY (
                INSTANCE_ID,
                OP_TYPE,
                SOT_TABLE,
                SOT_CREATE_DATE,
                SOT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                SEQ_ID,
                ITEM_TYPE_FLAG
            )
            VALUES (
                INSTANCE_ID,
                OP_TYPE,
                'L_SOT_TABLE_NAME',
                NRT_CREATION_DATE,
                NRT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                SEQ_ID,
                ITEM_TYPE_FLAG
            )

        -- 5

        WHEN OP_TYPE = 'U'
             AND ( ( ITEM_TYPE_FLAG IN (
            'P',
            'S'
        )
                     AND OLD_ITEM_TYPE_FLAG IN (
            'C'
        ) )
                   OR ( ITEM_TYPE_FLAG IN (
            'C'
        )
                        AND OLD_ITEM_TYPE_FLAG IN (
            'P',
            'S'
        ) ) ) THEN
            INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
                COVERED_LINE_ID,
                INSTANCE_ID,
                SEQ_ID,
                OP_TYPE,
                SOT_TABLE,
                SOT_CREATE_DATE,
                SOT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                CONTRACT_ID,
                ITEM_TYPE_FLAG
            )
            VALUES (
                COVERED_LINE_ID,
                INSTANCE_ID,
                SEQ_ID,
                'D',
                'L_SOT_TABLE_NAME',
                NRT_CREATION_DATE,
                NRT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                CONTRACT_ID,
                OLD_ITEM_TYPE_FLAG
            )

        -- 6

        WHEN OP_TYPE = 'U'
             AND ( ( ITEM_TYPE_FLAG IN (
            'P',
            'S'
        )
                     AND OLD_ITEM_TYPE_FLAG IN (
            'C'
        ) )
                   OR ( ITEM_TYPE_FLAG IN (
            'C'
        )
                        AND OLD_ITEM_TYPE_FLAG IN (
            'P',
            'S'
        ) ) ) THEN
            INTO XXCCS_UEE_DEVC_ES_CVD_KEY (
                INSTANCE_ID,
                OP_TYPE,
                SOT_TABLE,
                SOT_CREATE_DATE,
                SOT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                SEQ_ID,
                ITEM_TYPE_FLAG
            )
            VALUES (
                INSTANCE_ID,
                'D',
                'L_SOT_TABLE_NAME',
                NRT_CREATION_DATE,
                NRT_LAST_UPDATE_DATE,
                GG_CREATE_DATE,
                SEQ_ID,
                OLD_ITEM_TYPE_FLAG
            )

        -- 7

    ELSE
        INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
            COVERED_LINE_ID,
            INSTANCE_ID,
            SEQ_ID,
            OP_TYPE,
            SOT_TABLE,
            SOT_CREATE_DATE,
            SOT_LAST_UPDATE_DATE,
            GG_CREATE_DATE,
            CONTRACT_ID,
            ITEM_TYPE_FLAG
        )
    VALUES (
        COVERED_LINE_ID,
        INSTANCE_ID,
        SEQ_ID,
        OP_TYPE,
        'L_SOT_TABLE_NAME',
        NRT_CREATION_DATE,
        NRT_LAST_UPDATE_DATE,
        GG_CREATE_DATE,
        CONTRACT_ID,
        ITEM_TYPE_FLAG
    )
SELECT
    ROWID,
    COVERED_LINE_ID,
    OP_TYPE,
    SEQ_ID,
    NRT_CREATION_DATE,
    NRT_LAST_UPDATE_DATE,
    CONTRACT_ID,
    SERVICE_LINE_ID,
    GG_CREATE_DATE,
    INSTANCE_ID,
    ITEM_TYPE_FLAG,
    NVL(OLD_ITEM_TYPE_FLAG, ITEM_TYPE_FLAG) OLD_ITEM_TYPE_FLAG
FROM
    XXCCS_DS_CVDLIB_DTLEB_ES;

END PROCESS_ASSETS;
/

--

--------- UPDATE 2 ** **---------

--

I have updated the answer but as there is a limit of the 30000 characters(in Answer section) in StackOverflow, I have put the answer here.

Added/Modified the sections -- 7, 8, 9, 10 in newly updated answer

Cheers!!

thanks for giving this idea. But in last else block, there is no condition. Like if above conditions not satisfied then it will go to last else block. So, what condition i will write in select statement — nodev_101, Jul 01 '19 at 12:17
Can you please edit your question and include the mentioned code? — Popeye, Jul 01 '19 at 12:26
Thanks! Its really good approch to proceed. I need one last approach. I have another IF-ELSE condition for 2nd destination table in last ELSE block. Can you please help me on that. I have edited the code. — nodev_101, Jul 02 '19 at 08:33
in Last join query, INSTANCE_ID = L_GET_UNPROCESSED_RCD(I).INSTANCE_ID, but we already removed cursor loop right, then i think INSTANCE_ID value will not be fetched by this. — nodev_101, Jul 03 '19 at 04:43
Yes, You are right. I have updated the link. Please check now. We actually don't need this `WHERE` clause. — Popeye, Jul 03 '19 at 05:56
great.. Now its meeting the requirement. You are awesome tejash. Thank you very much — nodev_101, Jul 03 '19 at 06:31

/*+ APPEND_VALUES */ or /*+ APPEND*/ , which one is efficient one for insert operation in oracle

3 Answers3

--

--

--

--

/+ APPEND_VALUES / or /+ APPEND/ , which one is efficient one for insert operation in oracle