Re: how to get the best out of BULK opers?

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Mon, 10 Feb 2003 16:41:08 +0300
Message-ID: <b28a94$513$1@babylon.agtel.net>

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:b203eo$1807al$1_at_ID-114658.news.dfncis.de...

> a simple INSERT/UPDATE/MERGE INTO...SELECT would
> several times outperform it...

This is expected and normal.

>worse a very simplistic loop like
>
> FOR c1 IN CursorX
> LOOP
> INSERT/UPDATE/MERGE INTO
> // using collections
> END LOOP;
>
> many times also outperform the bulking operation when the number
> of iterations is high (above 100 I would say)...

Can you prove it? The simple test below shows that bulk operations are way faster than ordinary loops:

SQL> CREATE TABLE BULK_TEST AS
2 SELECT OBJECT_ID,

  3        OBJECT_NAME,
  4        OBJECT_TYPE

  5 FROM ALL_OBJECTS
  6 WHERE 1=0
  7 /

Table created.

SQL> INSERT INTO BULK_TEST
2 SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE 3 FROM ALL_OBJECTS
4 /

22789 rows created.

Elapsed: 00:00:02.13

SQL> ROLLBACK; Rollback complete.

SQL> DECLARE
  2 TYPE OBJID_T IS TABLE OF ALL_OBJECTS.OBJECT_ID%TYPE   3 INDEX BY BINARY_INTEGER;
  4 TYPE OBJNAME_T IS TABLE OF ALL_OBJECTS.OBJECT_NAME%TYPE   5 INDEX BY BINARY_INTEGER;
  6 TYPE OBJTYPE_T IS TABLE OF ALL_OBJECTS.OBJECT_TYPE%TYPE   7 INDEX BY BINARY_INTEGER;
  8
  9 OIT OBJID_T;
10 ONT OBJNAME_T;
11 OTT OBJTYPE_T;
12
13 BEGIN
14 SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE 15 BULK COLLECT INTO OIT, ONT, OTT
16 FROM ALL_OBJECTS;
17 FORALL I IN OIT.FIRST..OIT.LAST
18 INSERT INTO BULK_TEST (OBJECT_ID, OBJECT_NAME, OBJECT_TYPE) 19 VALUES (OIT(I), ONT(I), OTT(I));
20 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows processed.'); 21 END;
22 /

22789 rows processed.

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.93

SQL> ROLLBACK; Rollback complete.

SQL> DECLARE
  2 ICNT PLS_INTEGER := 0;
  3 BEGIN
  4 FOR BT IN (SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE   5 FROM ALL_OBJECTS) LOOP
  6 INSERT INTO BULK_TEST (OBJECT_ID, OBJECT_NAME, OBJECT_TYPE)   7 VALUES (BT.OBJECT_ID, BT.OBJECT_NAME, BT.OBJECT_TYPE);   8 ICNT := ICNT + 1;
  9 END LOOP;
10 DBMS_OUTPUT.PUT_LINE(ICNT || ' rows processed.'); 11 END;
12 /

22789 rows processed.

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.14

Clearly, INSERT..SELECT is the winner, but bulk ops came second and the gap between 2nd and 3rd place is a lot larger than between 1st and 2nd. I didn't bother to grab stats difference, but I can assure you it's there, too.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

Received on Mon Feb 10 2003 - 07:41:08 CST