Re: Help with simple PL/SQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 13 Oct 2005 16:37:31 -0700
Message-ID: <1129246636.674181@yasure>

DFS wrote:
> DA Morgan wrote:
>

>>William Robertson wrote:
>>
>>>You can simplify it even firther with something like (untested):
>>>
>>>CREATE OR REPLACE PROCEDURE setlistorder
>>>    ( p_projectid INTEGER )
>>>IS
>>>    i INTEGER := 1;
>>>BEGIN
>>>    FOR r IN
>>>    (
>>>        SELECT projcpid, itemorder
>>>        FROM   table_project
>>>        WHERE  projid = projectid
>>>        ORDER BY itemorder
>>>    )
>>>    LOOP
>>>        UPDATE table_project
>>>        SET    itemorder = i
>>>        WHERE  projcpid = r.projcpid;
>>>
>>>        i := i + 1;
>>>    END LOOP;
>>>END;
>>>
>>>However I might have missed what all the variables were for. It also
>>>seems like something you should be able to do with a straight UPDATE
>>>statement.
>>
>>Why would you use a cursor loop to do what could be done 100X faster
>>with array processing?

>
>
> 100x sounds exaggerated, don't you think DA? Notice each iteration contains
> an update statement.
>
> In case you're interested, that loop will operate on no more than 300
> records. It currently executes in half a second or so.
>
> But I'm curious what the array processing code would look like.

No I don't and I'll prove it to you.

• step 1 CREATE TABLE parent ( part_num NUMBER(10), part_name VARCHAR2(15));
• step 2 CREATE TABLE child AS SELECT * FROM parent WHERE 1=2;
• step 3 DECLARE

j PLS_INTEGER := 1;
k parent.part_name%TYPE := 'Transducer';

BEGIN
   FOR i IN 1 .. 20000
   LOOP

     SELECT DECODE(k, 'Transducer', 'Rectifier',
     'Rectifier', 'Capacitor',
     'Capacitor', 'Knob',
     'Knob', 'Chassis',
     'Chassis', 'Transducer')
     INTO k
     FROM dual;

     INSERT INTO parent VALUES (j+i, k);

   END LOOP;
   COMMIT;
END;
/

• step 4 - this is your cursor loop CREATE OR REPLACE PROCEDURE slow_way IS

BEGIN
   FOR r IN (SELECT * FROM parent)
   LOOP

     INSERT INTO child
     VALUES
     (r.part_num, r.part_name);

   END LOOP;
   COMMIT;
END slow_way;
/

• step 5 - this is my array processing CREATE OR REPLACE PROCEDURE fast_way IS

TYPE myarray IS TABLE OF parent%ROWTYPE; l_data myarray;

CURSOR c IS
SELECT *
FROM parent;

BEGIN
   OPEN c;
   LOOP
     FETCH c BULK COLLECT INTO l_data LIMIT 500;

     FORALL i IN 1..l_data.COUNT
     INSERT INTO child VALUES l_data(i);

     EXIT WHEN c%NOTFOUND;

   END LOOP;
   COMMIT;
   CLOSE c;
END fast_way;
/

• step 6 - put it to a test set timing on exec slow_way exec fast_way

I get the following:
1.29 seconds with a cursor loop
0.07 second with array processing
for 20,000 records.

SELECT 1.29/.07 FROM dual;

That is > 18X faster. I have seen it as high as ~100X in some circumstances with 9i. With 10g Oracle optimized the FETCH to use bulk collection so the change is less dramatic.

Play with the number of records in the table and the array size and your mileage will vary.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Thu Oct 13 2005 - 18:37:31 CDT