performance issue

I have noticed a major drop in insertion performance on an indexed table compared to when the table is not indexed. To quote my benchmark numbers, I get around 90 row insertions per second when I don't have any indexes on the table, but only around 14 insertions per second when I have an index.

Granted that these figures depend largely on the hardware/os/table specific details, but can anyone tell me whether such a performance drop is normal, or can I do something to improve the performance.

For Oracle to be viable I am looking for about 40-50 insertions per second with one index on the table. We have about a million rows to add in a window of 6-7 hours. I cannot drop the index just before doing the insertions and then add it back at the end for all kinds of reasons.

I am running Oracle 7.0.16 on a Sparc-10 running Solaris 2.3. The table has about 20 columns. The total size of each row is about 250 bytes. The column to be indexed is a character field of 20 bytes. There are about 50 duplicates in the table to the indexed column that come at random intervals. When running my benchmark I did a COMMIT WORK at the end of every 500 insertions. I can gladly provide any additional data needed.

I would appreciate email at my address below in addition to an optional posting on the net because our newsfeed is a lot flakier than our mailfeed and I would not want to miss any feedback on the matter.

Thanks in advance for your replies.

• allwyn -- Allwyn Carvalho | IA Corporation | If it were done when 'tis done allwyn_at_ia-us.com | 1900 Powell St | then 'twere well it was done Tel:(510)450-6886 | Emeryville | quickly. Fax:(510)450-7099 | CA 94608 | -- Shakespeare