1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
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;