Insertion performance

I'm concerned with the speed of INSERTs in Oracle 7, and I wonder if anyone can tell me whether the results I've been getting are typical.

Environment -

DB: Oracle 7.3.4
OS: Solaris 5.6
Machine: SPARCstation-20, 166 MHz single CPU

Test harness (running on same machine as Oracle server) -

C program, using the OCI API, inserts 10,000 rows into a table of 6 columns
(4 integer, 2 floating point), average row size about 30 bytes, two indexes
(2 column primary key index, 1 column non-unique inversion entry index). An
array of structures is used for the insertions, in which 256 rows are sent with each execution of the insertion cursor. The insert statement is parsed once, and executed repeatedly until all rows have been inserted. The process is performed as a single database transaction. The test table resided in a pre-extended tablespace extent.

Results -

Load 10,000 rows: 200 rows/second (with indexes)

                                    2500 rows/second (without indexes)

These results were a great surprise, because I had first performed the test with the MySQL relational database system, an essentially free product from T.c.X. in Sweden, which supports a subset of ANSI SQL. The results with MySQL were

Load 10,000 rows: 2000 rows/second (with indexes)

                                  5000 rows/second (without indexes)

The MySQL database resided on the same disk which contained the Oracle test table.

The full order of magnitude difference with indexed inserts was especially surprising, since MySQL does not support prepared statements - each insertion command was an ASCII SQL command string, with data for about 110 rows. Transactions are not supported by MySQL, so some overhead may have been saved there.

The bottleneck in Oracle seems to be the indexes, since their presence reduced throughput by 90%. Both Oracle and MySQL use B-tree indexes, so it's difficult to understand why Oracle is so much slower.

Is this typical of Oracle? Is it the price we pay for all the bells and whistles? Or is there a way to speed up inserts significantly? I'd be very interested to hear of others' experiences.

• Greg Johnson Cabletron Systems