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;
/

Boneist · Answer 1 · 2019-06-11T11:02:24.100

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.

edited Jun 11 '19 at 11:02

answered Jun 11 '19 at 10:56

Boneist

22,910
1
25
40

Thank you ,the query execution time was enhanced used this script. I'm wondering though why the sequence numbers are reused for each of the target table , is it because you used insert all ? Also why do I have to disable the FK constrain if the same id is used ? – ghostman Jun 12 '19 at 12:08
Sequences are not recommended to use with INSERT ALL (just because it works for the current version doesn't mean it will work for future versions), as things behave strangely - see that link I put in my answer. As for the foreign key constraints - it's only if there are foreign keys between the tables being inserted into, as there is no order-of-insert in an `INSERT ALL` statement - e.g. a row might be inserted into table_2 before or after an insert into table_3, etc. – Boneist Jun 12 '19 at 12:31
But how can I handle it in my case ? what do you mean by "create a table to hold the contents of the select statement, along with the sequence number " ? how I'm supposed the load the contents inside the staging table without insert ? – ghostman Jun 12 '19 at 12:48
You do `create table xyz as select seq_id.nextval object_id,... ` (the select statement is essentially the same as the select statement in the insert all statement in my answer, just with the extra column getting the sequence numbers. Once you have that, the select statement in the insert all becomes `select object_id, text1, text2, res_id from xyz`, and you'd have to replace the `seq_id.nextval` calls with `object_id`. This is safer, but will be a bit slower, due to the extra insert into and read of that table. Not as slow as your original procedure, though, I think. – Boneist Jun 12 '19 at 12:52

score -1 · Answer 2 · answered Jun 11 '19 at 08:14

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;
/

I tried the script you provided , but it is still taking the same time. — ghostman, Jun 11 '19 at 08:43
In my script, I have reduced around 16000+ function calls. It must reduce the time of execution. Can you please compare the execution plans and actual time using db time.(Printing time before and after execution) — Popeye, Jun 11 '19 at 08:54

oracle Cursor + FORALL taking time

2 Answers2