Re: Performance effect of adding a new index

On Apr 10, 10:37 pm, "Sam" <sasan.raisd..._at_mobidia.com> wrote:
> Hi there,
> I have a table with about 10-20 insert per minutes and it can carry up to
> 500,000 records, except the primary key this table doesn't have any index,
> I need to make some reports based on a field that's System Date, this report
> shows in a web interface and run a few times per day,
> I heard that adding index on table with lot's of insert downgrade the insert
> performance,
> How much do you think adding this index effect the general performance of
> this database? What can I do to minimize the performance effect?
>
> Server Configuration:
> Oracle 10g
> Windows 2003
> No Raid
>
> Thank you in advance - Sam

If this is a table in oracle and there's really a primary key defined then it does have an index.

It doesn't sound though as if you are planning or thinking about changing that field anyway are you thinking about indexing some other field?

As opposed to the opinions made by some posters recently in cdos, any changes in the indexes for a table can potentially affect quite a few different parts of your existing applications.

You need to think carefully about what SQL statements will be improved by adding the index, and which statements will be negatively impacted. Does the index need to potentially be added and dropped around a batch cycle processing requirement?

Along with the thoughts included above, the first question needs to be "do you have a test system available and how will you measure/quantify the pro's and con's of adding this index before thinking about any changes to a production system". Well maybe the first question should be "do you have a dba to work with or a lead application developer that ( one or both ) should get involved". Received on Wed Apr 11 2007 - 09:36:48 CDT