Re: performance issue

Allwyn Carvalho (allwyn_at_lia.com) wrote:
> 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.

Insertion on an index is very expensive when compared to non-indexed insertion. To maintain an index oracle has to traverse it to find the correct place to put the key. That can move the disk-head "around" to cause some overhead.

> 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 think thats what you should do. Is your data pre-sorted? if so you may be able to speed up the index-creation.

--
**************************************************************
*                          Saad Ahmad                        *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************