1. Get unlimited access to documents by upgrading to Premium Membership.
  2. We are not accepting new members currently. If you would like to join, please contact us.
    Dismiss Notice
nageswarareddy

For all 2012-05-29

For all

  1. nageswarareddy
    Simple example for FORALL loop performance

    CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));

    DECLARE
    TYPE numtab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
    TYPE nametab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
    pnums numtab;
    pnames nametab;
    t1 NUMBER(9);
    t2 NUMBER(9);
    t3 NUMBER(9);
    BEGIN
    FOR j IN 1..5000 LOOP -- load index-by tables
    pnums(j) := j;
    pnames(j) := 'Part No. ' TO_CHAR(j);
    END LOOP;

    t1 := dbms_utility.get_time;
    FOR i IN 1..5000 LOOP -- use FOR loop
    INSERT INTO parts VALUES (pnums(i), pnames(i));
    END LOOP;
    t2 := dbms_utility.get_time;

    FORALL i IN 1..5000 -- use FORALL statement
    INSERT INTO parts VALUES (pnums(i), pnames(i));
    t3 := dbms_utility.get_time;

    dbms_output.put_line('Execution Time (secs)');
    dbms_output.put_line('---------------------');
    dbms_output.put_line('FOR loop: ' TO_CHAR(t2 - t1));
    dbms_output.put_line('FORALL: ' TO_CHAR(t3 - t2));
    END;