oracle Cursor + FORALL taking time

Question

I wrote the script below in Oracle that inserts thousands of rows in a table and uses the auto-generated id resulted from these rows and uses it in other inserts.

The script is working as expected but the issue is that it takes time to finish. Following are some details of the contents of each table currently:

• table_0 contains 16000+ rows
• table_1 contains 4000+ rows

With these volumes the script takes around 15 to 20 seconds. The issue is that I intend to use a similar query to handle Millions of rows.

Here is the code for a function called by the script:

create or replace FUNCTION get_id (name1 IN varchar2) RETURN INTEGER
as res_id INTEGER;
begin

select id  into res_id from table_1 where node_type='type1' and name = 
 name1;

return res_id;
end;
/

Here is the script itself:

DECLARE

  TYPE rt IS RECORD (text1 varchar2(20),text2 varchar(20));

  TYPE texts_tab IS TABLE OF rt;
  TYPE ids_tab   IS TABLE OF table_1.id%TYPE;
  p_texts texts_tab;
  p_ids   ids_tab;
  id_2 integer;
  CURSOR c IS
    SELECT DISTINCT text1,text2 FROM table_0 order by text1,text2;
BEGIN
select FUNC1('type2') into id_2 from dual;
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO p_texts LIMIT 1000;

    FORALL i IN 1 .. p_texts.COUNT
       INSERT INTO table_2(object_id,object_type,parent_id)
        VALUES (SEQ_ID.NEXTVAL, id_2 ,get_id(p_texts(i).text1) ,0,0)
        RETURNING object_id BULK COLLECT INTO p_ids;

    FORALL i IN 1 .. p_ids.COUNT
      insert into table_3 (object_id,field2)
        VALUES ( p_ids(i), p_texts(i).text2 );

     FORALL i IN 1 .. p_ids.COUNT   
        insert into table_1 (node_type,text1,id)
    VALUES('type2', p_texts(i).text1 , p_ids(i));

    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
  COMMIT;
END;
/

Answer 1

I have removed the function call and incorporated that function result into direct cursor query.

Actually, call to function for each row of the cursor is slowing down the performance, That is what I think.

Can you please try the following code and share the result of the outcome:

DECLARE
    TYPE RT IS RECORD (
        TEXT1     VARCHAR2(20),
        TEXT2     VARCHAR(20),
        ID        VARCHAR2(20) -- SET IT ACCORDING TO YOUR DATA TYPE AND SIZE
    );
    TYPE TEXTS_TAB IS
        TABLE OF RT;
    TYPE IDS_TAB IS
        TABLE OF TABLE_1.ID%TYPE;
    P_TEXTS   TEXTS_TAB;
    P_IDS     IDS_TAB;
    ID_2      INTEGER;
    -- CHANGED THIS CURSOR TO REMOVE FUNCTION
    CURSOR C IS
    SELECT DISTINCT
        T0.TEXT1,
        T0.TEXT2,
        T1.ID
    FROM
        TABLE_0 T0,
        TABLE_1 T1
    WHERE
        T1.NODE_TYPE = 'type1'
        AND T1.NAME = T0.TEXT1
    ORDER BY
        TEXT1,
        TEXT2;

BEGIN
    SELECT
        FUNC1('type2')
    INTO ID_2
    FROM
        DUAL;

    OPEN C;
    LOOP
        FETCH C BULK COLLECT INTO P_TEXTS LIMIT 1000;
        FORALL I IN 1..P_TEXTS.COUNT
            INSERT INTO TABLE_2 (
                OBJECT_ID,
                OBJECT_TYPE,
                PARENT_ID
            ) VALUES (
                SEQ_ID.NEXTVAL,
                ID_2,
                P_TEXTS(I).ID, -- ADDED DIRECTLY ID INSTEAD OF FUNCTION CALL
                0,
                0
            ) RETURNING OBJECT_ID BULK COLLECT INTO P_IDS;

        FORALL I IN 1..P_IDS.COUNT
            INSERT INTO TABLE_3 (
                OBJECT_ID,
                FIELD2
            ) VALUES (
                P_IDS(I),
                P_TEXTS(I).TEXT2
            );

        FORALL I IN 1..P_IDS.COUNT
            INSERT INTO TABLE_1 (
                NODE_TYPE,
                TEXT1,
                ID
            ) VALUES (
                'type2',
                P_TEXTS(I).TEXT1,
                P_IDS(I)
            );

        EXIT WHEN C%NOTFOUND;
    END LOOP;

    CLOSE C;
    COMMIT;
END;
/

Answer 2

I think you can do this more simply using INSERT ALL, e.g.:

DECLARE
  id_2    INTEGER;
BEGIN
  id_2 := func1('type2');

  INSERT ALL
    INTO table_2 (object_id, object_type, parent_id) VALUES (seq_id.nextval, id_2, res_id)
    INTO table_3 (object_id, field2) VALUES (seq_id.nextval, text2)
    INTO table_1 (node_type, text1, ID) VALUES ('type2', text1, seq_id.nextval)
  SELECT t0.text1,
         t0.text2,
         t1.id AS res_id
  FROM   (SELECT DISTINCT text1,
                          text2
          FROM   table_0) t0
         LEFT OUTER JOIN table_1 t1 ON t0.text1 = t1.name AND t1.node_type = 'type1';

  COMMIT;
END;
/

I knocked up a simple test case to show that the sequence numbers generated are reused for each of the target tables for each source row.

If there are foreign keys between the tables, you may need to disable them before the insert and re-enable them afterwards.

N.B. using a sequence inside an INSERT ALL is generally not recommended (e.g. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532591900346482312), so you may do better to create a table to hold the contents of the select statement, along with the sequence number and then use that new table in the insert all.