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

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?
>

Err... 200k rows inserted in .65 secs is 200k/.065 = 307692.30769230769230769230769231 rows/sec in my calc. I'll go for 307692 :)

And those are inserts - no asked for.
But nice to know; my home-brew Intel E6600 PC with SATA drive:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> set timing on
SQL> exec fast_way

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL> select count(*) from child;

  COUNT(*)

    200000

769230 inserts/sec

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...