CODEWITHSUNDEEP
sqloracle-databaseplsql
districktt
districktt

Reputation: 81

Is there any way I can speed up the following insert(s)?

Is there any way of speeding up this piece of code?

FOR j IN 1..max
LOOP
  INSERT INTO myTable VALUES(DBMS_RANDOM.VALUE(1, 500), DBMS_RANDOM.VALUE(1, 500));
END LOOP;

The loop is gonna happen for at least a million times, so a million+ inserts. I know about FORALL, but that is for collections of data that already exists.. not really helpful for me. So I was wondering if there's a better way than a classic loop, or at least some other improvements that I can do(like adding/removing some options when I create the table), anything that can make this run faster.

Thanks in advance!

Upvotes: 1

Views: 137

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

A million rows inserted during a single transaction is going to use up a lot of space in your UNDO tablespace. A better choice IMO is to use some intermediate commits to reduce the span of the transactions. Thus, building on @ReneNyffenegger's answer, I suggest something like the following:

DECLARE
  nRows_inserted  NUMBER := 0;
BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO MY_TABLE
      SELECT DBMS_RANDOM.VALUE(1, 500),
             DBMS_RANDOM.VALUE(1, 500)
        FROM DUAL
        CONNECT BY LEVEL <= 1000;

    nRows_inserted := nRows_inserted + SQL%ROWCOUNT;

    COMMIT;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(nRows_inserted || ' rows inserted into MY_TABLE');
END;

Share and enjoy.

Upvotes: 5

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40499

You might want to try to use a single insert statement instead of executing a statement a million times:

insert into myTable
  select
    dbms_random.value(1, 500),
    dbms_random.value(1, 500)
  from
    dual
  connect by
    level <= 1*1000*1000;

On a side note: did you measure how much time is spent on executing dbms_random and how much time on actually inserting the values?

Upvotes: 10

Related Questions