Re: Poll: How fast is YOUR database in rows per second, on a full bore table scan???

Frank van Bortel wrote:
> DA Morgan wrote:
>

>> SQL>
>> SQL> SELECT COUNT(*) FROM parent;
>>
>>   COUNT(*)
>> ----------
>>     200000
>>
>> SQL> SELECT COUNT(*) FROM child;
>>
>>   COUNT(*)
>> ----------
>>          0
>>
>> SQL> CREATE OR REPLACE PROCEDURE fast_way IS
>>   2
>>   3  TYPE myarray IS TABLE OF parent%ROWTYPE;
>>   4  l_data myarray;
>>   5
>>   6  CURSOR r IS
>>   7  SELECT part_num, part_name
>>   8  FROM parent;
>>   9
>>  10  BEGIN
>>  11    OPEN r;
>>  12    LOOP
>>  13      FETCH r BULK COLLECT INTO l_data LIMIT 1000;
>>  14
>>  15      FOR j IN 1 .. l_data.COUNT
>>  16      LOOP
>>  17        l_data(1).part_num := l_data(1).part_num * 10;
>>  18      END LOOP;
>>  19
>>  20      FORALL i IN 1..l_data.COUNT
>>  21      INSERT INTO child VALUES l_data(i);
>>  22
>>  23      EXIT WHEN r%NOTFOUND;
>>  24    END LOOP;
>>  25    COMMIT;
>>  26    CLOSE r;
>>  27  END fast_way;
>>  28  /
>>
>> Procedure created.
>>
>> SQL> set timing on
>> SQL> exec fast_way
>>
>> PL/SQL procedure successfully completed.
>>
>> Elapsed: 00:00:00.65
>> SQL> set timing off
>> SQL>
>>
>> Only 85,000 rows per second?
>>

Part of the reason I was so amazed anyone would throw out the number 58K rows per second for an FTE. I can scan, process, and insert ten times that number of rows on a production system with decent code.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org