Ken Denny wrote:
> On Nov 1, 3:49 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> Ken Denny wrote:
>>> On Nov 1, 11:26 am, DA Morgan <damor..._at_psoug.org> wrote:
>>>> Now my examples does not include a cursor loop. But if you have
>>>> DMBS_ERRLOG then you are in a version of Oracle more recent than 8i.
>>>> And that means you've no reason for ever writing a cursor loop: Ever!
>>> It can save you a lot of typing. Is it worth an extra 1000 keystrokes
>>> in code to save one or two microseconds in execution time?
>> Lets see ... with cursor loop.
>>
>> CREATE OR REPLACE PROCEDURE slow_way IS
>>
>> BEGIN
>> FOR r IN (SELECT * FROM parent) LOOP
>> -- modify record values
>> r.part_num := r.part_num * 10;
>> -- store results
>> INSERT INTO child
>> VALUES
>> (r.part_num, r.part_name);
>> END LOOP;
>> COMMIT;
>> END slow_way;
>> /
>>
>> -- 7.71 seconds
>>
>> without cursor loop.
>>
>> CREATE OR REPLACE PROCEDURE fast_way IS
>>
>> TYPE myarray IS TABLE OF parent%ROWTYPE;
>> l_data myarray;
>>
>> CURSOR r IS
>> SELECT part_num, part_name
>> FROM parent;
>>
>> BEGIN
>> OPEN r;
>> LOOP
>> FETCH r BULK COLLECT INTO l_data LIMIT 1000;
>>
>> FOR j IN 1 .. l_data.COUNT
>> LOOP
>> l_data(1).part_num := l_data(1).part_num * 10;
>> END LOOP;
>>
>> FORALL i IN 1..l_data.COUNT
>> INSERT INTO child VALUES l_data(i);
>>
>> EXIT WHEN r%NOTFOUND;
>> END LOOP;
>> COMMIT;
>> CLOSE r;
>> END fast_way;
>> /
>>
>> -- 0.50 seconds
>>
>> <SARCASM>
>> Oh my gawd my fingers are killing me. All the heavy lifting and hard
>> work. Please excuse me for a minute. I need to sit down and catch my
>> breath ... make an appointment to have my carpel tunnel syndrome
>> treated ... make out my will.
>>
>> And all I did was speed the code up by a lousy 15.42 times. Hardly
>> worth it eh.
>> </SARCASM>
>>
>> Good thing the web is near anonymous Ken. You might have just talked
>> yourself out of quite a few job opportunities if recruiters look for
>> you on the web. A few microseconds my ....
>
> Sorry. I didn't mean to imply that the savings was never more than a
> few microseconds. There are many times where the savings using bulk
> collect and avoiding cursor loops are enormous. What I meant to say
> was that *SOMETIMES* the savings are minimal while the increase in
> keystrokes are huge. Consider in your example if your insert had to
> reference 100 or more individual columns and your parent table had no
> more than 100 rows.
The number of extra keystrokes if fixed:
FETCH cursor1 INTO record1
replaced with
FETCH r BULK COLLECT INTO l_data LIMIT 1000;
and the limit clause is optional.
INSERT INTO child (col1, col2, col3) VALUES (val1, val2, val3)
rather than
FORALL i IN 1..l_data.COUNT
INSERT INTO child VALUES l_data(i);
I think you will find it actually saves keystrokes.
But to assume that milliseconds don't matter is to assume that
you have a single user system and that the amount of time
latches are held doesn't affect other activities on the system.
I wouldn't make that assumption.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Nov 07 2007 - 15:09:59 CST
